Hello and good day!
I am stumped and not very familiar with macros, so looking to learn! I have an excel file and on one sheet one, I have a column of codes (in the file I am working on had 96 product codes), we'll call this product code sheet. On sheet two, I have 6048 rows of data, we'll call this data sheet. I am trying to take one code from the code sheet, apply the 6048 rows and then repeate this for each code in the code sheet without having to copy and paste, over and over and over...creating a sheet that will have 145152 rows of data. Someone created a macro dubbed the file expander, but it doesn't like to go beyond creating 30,000 rows.
Sub ProductCodeExpander()
Dim ProductCode As String
Dim RangeToCopy As Range
Dim index As Integer
index = 2
Set RangeToCopy = Sheets("Data Sheet").Range("B2:I6049") 'Number of rows on Data Sheet tab. This has to be changed every time I run the macro because every file I work with is different'
ProductCode = Sheets("Product Codes").Range("A1").Value
Sheets("Data Sheet").Range("A2:A6049").Value = ProductCode 'Number of rows on change request tab. This has to be changed every time I run the macro because every file I work with is different'
Do While index <= 24 'Number of Product Codes'
ProductCode = Sheets("Product Codes").Range("A" & index).Value
Sheets("Data Sheet").Range("A" & ((index - 1) * 6048) + 2 & ":A" & (index * 6048) + 1).Value = ProductCode
'Number of codes not including header. This has to be changed every time I run the macro because every file I work with is different'
Sheets("Data Sheet").Range("B" & ((index - 1) * 6048) + 2 & ":I" & (index * 6048) + 1).Value = RangeToCopy.Value
index = index + 1
'Number of codes not including header. This has to be changed every time I run the macro because every file I work with is different'
Loop
End Sub
The file I have attached is just an example, a small sampling.
Please help!
Thank you in advance!
Ginger
I am stumped and not very familiar with macros, so looking to learn! I have an excel file and on one sheet one, I have a column of codes (in the file I am working on had 96 product codes), we'll call this product code sheet. On sheet two, I have 6048 rows of data, we'll call this data sheet. I am trying to take one code from the code sheet, apply the 6048 rows and then repeate this for each code in the code sheet without having to copy and paste, over and over and over...creating a sheet that will have 145152 rows of data. Someone created a macro dubbed the file expander, but it doesn't like to go beyond creating 30,000 rows.
Sub ProductCodeExpander()
Dim ProductCode As String
Dim RangeToCopy As Range
Dim index As Integer
index = 2
Set RangeToCopy = Sheets("Data Sheet").Range("B2:I6049") 'Number of rows on Data Sheet tab. This has to be changed every time I run the macro because every file I work with is different'
ProductCode = Sheets("Product Codes").Range("A1").Value
Sheets("Data Sheet").Range("A2:A6049").Value = ProductCode 'Number of rows on change request tab. This has to be changed every time I run the macro because every file I work with is different'
Do While index <= 24 'Number of Product Codes'
ProductCode = Sheets("Product Codes").Range("A" & index).Value
Sheets("Data Sheet").Range("A" & ((index - 1) * 6048) + 2 & ":A" & (index * 6048) + 1).Value = ProductCode
'Number of codes not including header. This has to be changed every time I run the macro because every file I work with is different'
Sheets("Data Sheet").Range("B" & ((index - 1) * 6048) + 2 & ":I" & (index * 6048) + 1).Value = RangeToCopy.Value
index = index + 1
'Number of codes not including header. This has to be changed every time I run the macro because every file I work with is different'
Loop
End Sub
The file I have attached is just an example, a small sampling.
Please help!
Thank you in advance!
Ginger
