Macro skipping third row

[Foxvh]

Hi the code below does exactly what i want it to do except it always skips the third row for some reason.

    Private Sub CommandButton1_Click()
        Dim c As Range
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        For Each c In ActiveSheet.Range("A1", "A" & LastRow)
            If c.Value <> "" Then
                c.Offset(0, 0).Select
                    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 14).Cut _
                        Destination:=Worksheets("Pivot Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            End If
        Next c
    End Sub

 

[CephasOz]

In your Destination you use Rows.Count, but because the ActiveSheet is not Pivot Data, that is the number of rows in the ActiveSheet. So you are possibly overwriting one row's data with another. Try this edit of your code, and see if it produces the desired result. (Always use a copy of of your workbook, just in case.)

Private Sub CommandButton1_Click()
    Dim c As Range
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In ActiveSheet.Range("A1", "A" & LastRow)
        If c.Value <> "" Then
            With Worksheets("Pivot Data")
                c.Resize(, 15).Cut Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            End With
        End If
    Next c
End Sub

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim c As Range
   Dim LastRow As Long
   LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
   For Each c In ActiveSheet.Range("A1:A" & LastRow)
      If c.Value <> "" Then
         c.Resize(, 15).Copy = Worksheets("Pivot Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
         c.Resize(, 15).Clear
      End If
   Next c
End Sub

 

[Foxvh]

Sorry i should have added a bit more detail.
by skipping the third row i mean when i run the code it cut&pastes row 2,4,5,6+ and for some reason it skips over the 3rd row in the activesheet when cutting the data.

Hopefully that image helps.

 

[Fluff]

Have you tried, either of the codes provided?

 

[Foxvh]

I tried both the first code still leaves the third row (i change the For Each Line to "A2' as i have headers for that worksheet.
the second code has an (Object Required) on the below line
(c.Resize(, 15).Copy = Worksheets("Pivot Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

 

[Fluff]

Oops, on my code, you need to remove the = sign on the line that fails.

 

[CephasOz]

Did you copy the code, or re-type it? Use the copy icon in the top-right of the post to copy the code, then paste it into the appropriate place. Otherwise, you might miss typing something.

 

[Fluff]

@CephasOz
Did you actually try your code?

 

[Foxvh]

Thanks Fluff your code works perfectly for what i need.