I have the following code:
Sub CopyTest()
Dim skuRow As Integer
Dim curSku As String
Dim numSkus As Integer
Dim impType As String
Dim copyRows As Integer
Dim supAcc As String
Dim arr_TotalList As Variant
Dim locs As String
Dim colorMax As String
With ActiveSheet
copyRows = 3
impType = "-LE"
supAcc = ""
numSkus = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row
skuRow = 1
Rows("1:" & copyRows).Copy
For i = copyRows + 1 To (copyRows * numSkus) + 1 Step copyRows
arr_TotalList = Split(CurVal, "|")
colorMax = Sheets(2).Range("C" & skuRow).Value
If colorMax = "" Then
colorMax = "4"
End If
curSku = Sheets(2).Range("A" & skuRow).Value
locs = Sheets(2).Range("B" & skuRow).Value
arr_TotalList = Split(locs, "|")
Range("A" & i).Select
ActiveSheet.Paste
For n = 0 To copyRows - 1 Step 1
If n = 0 Then
Range("B" & i + n) = supAcc & curSku & impType
Range("E" & i + n) = colorMax
Else
Range("G" & i + n) = arr_TotalList(n - 1)
Range("B" & i + n) = supAcc & curSku & impType
End If
Next n
skuRow = skuRow + 1
Next i
End With
End Sub
This code basically uses a data template in Sheet 1 and copies that template and implements data from Sheet 2 how ever many times set in "copyRows"
Right now, I would have to adjust the template in Sheet 1 each time I change "copyRows" number. For example if copyRows is 4, in Sheet 1, the template would contain 4 rows of predefined stuff, the first row is required, the following rows is based on the "|" delimiter of values in Sheet 2's data. For example, Sheet 2 column B has data like, "Test1|Test2|Test3"
I am trying to automate this code a little further so I do not have to manually change copyRows and add or remove additional rows in the template sheet. In the template sheet, I want to just have two rows, the first is required, the second will be based on the number of "|" delimiter values.
I am thinking of adding a count formula for the copyRows, for example, =LEN(Sheets(2).B:B)-LEN(SUBSTITUTE(Sheets(2).B:B,"|",""))
and then adding 1. How would I put in the formula in for copyRows?
ubound(arr_TotalList)
should be the upper bound of the index of arr_totalList after you perform the Split on "|". If I remember correctly, Ubound would normally report the index count which is normally 1 greater than the index (sox(Ubound(x)-1)
would be the highest index), but on a Variant it returns the highest index instead (sox(ubound(x)
is highest). [Check this]arr_TotalList
defined yet when you're settingcopyRows
up, you could setcopyRows = 1 + UBound(Split(Sheets(2).Range("B" & skuRow).Value))
though.