For...Next: Next c not advancing through the range.

[Yamezz]

The copy and pasting is doing what I want, but I'm having trouble advancing to the next cell in the Quantity column, to repeat my copy and paste. Eventually (when I stumble through this problem) I will need to do the pasting of the data from my 'next c in range' to the next row on the Invoice sheet. One problem at a time though I suppose.

I'm also confused about the last few rows of code - if the cell is not >1 (i.e. it's blank) I want the code to move onto the next cell in the Quantity range, but if I put a 'Next c' between my Else... and End If, I get an error.

Any help is much appreciated.

Worksheets("Job Card").Select
Dim Quantity As Range
Worksheets("Job Card").Range("Quantity").Select
For Each c In Range("Quantity") 'for each cell in the Quantity column
    If ActiveCell > 0 Then 'check if the value is greater than 0, if so then...
        ActiveCell.Select 'selects the Quantity
        Application.CutCopyMode = False
        Selection.Copy Sheets("Invoice").Range("A17") 'Copies Quantity to Invoice sheet
        ActiveCell.Offset(0, -2).Select 'move two c to left & select Item from Job Card
        Application.CutCopyMode = False
        Selection.Copy Sheets("Invoice").Range("B17") 'Copies Item to Invoice sheet
        ActiveCell.Offset(0, 3).Select 'move three c to right & select Total from Job Card
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Invoice").Range("C17").PasteSpecial xlValues  'Copies Total to Price column of Invoice sheet
    Else 'if Quantity is not >1
    End If
Next c 'move to the next cell in Quantity Range

 

[VoG]

Try

Dim c As Range
Worksheets("Job Card").Select
For Each c In Range("Quantity") 'for each cell in the Quantity column
    If c.Value > 0 Then 'check if the value is greater than 0, if so then...
        c.Copy Sheets("Invoice").Range("A" & Rows.Count).End(xlUp).Offset(1) 'Copies Quantity to Invoice sheet
        c.Offset(0, -2).Copy Sheets("Invoice").Range("B" & Rows.Count).End(xlUp).Offset(1) 'Copies Item to Invoice sheet
        c.Offset(0, 3).Copy 'move three c to right & select Total from Job Card
        Sheets("Invoice").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues 'Copies Total to Price column of Invoice sheet
    End If
Next c 'move to the next cell in Quantity Range

 

[Yamezz]

Thanks for your prompt reply Peter.
It's certainly a lot cleaner than my effort, but there's something unexpected going on.
The copied values appear under the last used row in each column (as I can see from your code, but I need to have the first lot of pasted values appear in row 17 of the Invoice sheet, and the next lot row 18 and so on. As it is, I have some data below the section where I want this copied data to be pasted, so with the code you supplied it's being copied underneath this extra data.
I take it it's a matter of some kind of i = i +1 counter thing that starts at row 17 and moves down.

 

[VoG]

Try

Dim c As Range, i As Long
i = 16
Worksheets("Job Card").Select
For Each c In Range("Quantity") 'for each cell in the Quantity column
    If c.Value > 0 Then 'check if the value is greater than 0, if so then...
        i = i + 1
        c.Copy Sheets("Invoice").Range("A" & i) 'Copies Quantity to Invoice sheet
        c.Offset(0, -2).Copy Sheets("Invoice").Range("B" & i) 'Copies Item to Invoice sheet
        c.Offset(0, 3).Copy 'move three c to right & select Total from Job Card
        Sheets("Invoice").Range("C" & i).PasteSpecial xlValues 'Copies Total to Price column of Invoice sheet
    End If
Next c 'move to the next cell in Quantity Range

 

[Yamezz]

Thanks Peter. I'm very slowly getting my head around this VBA, though at this stage I'm finding there's nothing basic about Visual Basic! Cheers.