Slight change required to Macro

[Batley]

Good afternoon,
I have the following macro that changes single lines of data into actuals (so 5 pallets for store A becomes 5 lines of 1 pallet for store A):

Sub Singles()
'
' Singles Macro
'
'Sub aTest()
    'Assumes data in columns A:D; headers in row 1
    'Results in columns F:I
    Dim vData As Variant, vResult As Variant
    Dim lNumRows As Long, i As Long, j As Long, lLin As Long
  
    vData = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
    lNumRows = Application.Sum(Application.Index(vData, 0, 5))
    vResult = Range("g2").Resize(lNumRows, 5)
      
    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 5)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = 1
            vResult(lLin, 4) = vData(i, 4)
            vResult(lLin, 5) = vData(i, 5)
        Next j
    Next i
    Range("g1:k1").Value = Range("A1:e1").Value
    Range("g2").Resize(lNumRows, 5) = vResult
    Columns("g:k").AutoFit
End Sub

My data set has changed so I have adjusted the macro to calculate the additional column's, however it is duplicating the lines based on the wrong column data (it currently duplicates the lines based on priority and I want it to look at pallets).

This is my data the macro uses (A:E is my data the macro produces G:K):

Store Number	Store Name	Pallets	Split	Priority		Store Number	Store Name	Pallets	Split	Priority
114	Jersey	9	DTS South	1		114	Jersey	1	DTS South	1
780	Maidstone	9	DTS South	6		780	Maidstone	1	DTS South	6
113	Chichester	5	DTS South	12		780	Maidstone	1	DTS South	6
139	Gateshead	6	DTS South	16		780	Maidstone	1	DTS South	6
540	Stockton	13	DTS South	22		780	Maidstone	1	DTS South	6
635	Banbury	8	DTS South	25		780	Maidstone	1	DTS South	6
595	Oxford	7	DTS South	26		780	Maidstone	1	DTS South	6
133	Crawley	1	DTS South	28		113	Chichester	1	DTS South	12
65	Horsham	7	DTS South	29		113	Chichester	1	DTS South	12
116	Farnborough	6	DTS South	30		113	Chichester	1	DTS South	12
870	Reading	11	DTS South	32		113	Chichester	1	DTS South	12

At the moment it is looking in col E to duplicate the lines, how do I make it look in col C?
As I had help creating this macro I am unable to change it correctly, as previously stated I managed to get it too accept the new column but in doing this is has moved my data and resulted in this error.
Any help would be greatly appreciated, I am sure I need to stop it looking a col 5 and change to col 3 but when I change it I get an error and it wont run.
Thanks
Sara

 

[Fluff]

How about

Sub Singles()
'
' Singles Macro
'
    'Assumes data in columns A:D; headers in row 1
    'Results in columns F:I
    Dim vData As Variant, vResult As Variant
    Dim lNumRows As Long, i As Long, j As Long, lLin As Long
  
    vData = Range("A2:E" & Cells(rows.Count, "A").End(xlUp).Row)
    lNumRows = Application.Sum(Application.Index(vData, 0, 5))
    ReDim vResult(1 To lNumRows, 1 To 5)
      
    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 3)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = 1
            vResult(lLin, 4) = vData(i, 4)
            vResult(lLin, 5) = vData(i, 5)
        Next j
    Next i
    Range("g1:k1").Value = Range("A1:e1").Value
    Range("g2").Resize(lNumRows, 5) = vResult
    Columns("g:k").AutoFit
End Sub

 

[mohadin]

Hi
Try

Option Explicit

Sub Singles()
'
' Singles Macro
'
'Sub aTest()
'Assumes data in columns A:D; headers in row 1
'Results in columns F:I
    Dim vData As Variant, vResult As Variant
    Dim lNumRows As Long, i As Long, j As Long, lLin As Long

    vData = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
    lNumRows = Application.Sum(Application.Index(vData, 0, 3))
    vResult = Range("g2").Resize(lNumRows, 5)

    For i = 1 To UBound(vData, 1)
        For j = 1 To vData(i, 3)
            lLin = lLin + 1
            vResult(lLin, 1) = vData(i, 1)
            vResult(lLin, 2) = vData(i, 2)
            vResult(lLin, 3) = vData(i, 3)
            vResult(lLin, 4) = vData(i, 4)
            vResult(lLin, 5) = vData(i, 5)
        Next j
    Next i
    Range("g1:k1").Value = Range("A1:e1").Value
    Range("g2").Resize(lNumRows, 5) = vResult
    Columns("g:k").AutoFit
End Sub

 

[Batley]

Thank you that works perfectly!

So just to clarify it is just one line that you changed:
For j = 1 To vData(i, 5) Original
For j = 1 To vData(i, 3) New

This is the line that tells it which column to look at (not col 5 but col 3)

 

[Fluff]

That's right.