VBA macro if criteria is met copy cells to another worksheet

[slohman]

I have this macro that I found but it gives me an error, I have need it to start in cell B3 not B3 as I have merged headers in Rows A1:H2

Sub Copy_Products()
Dim lastrow As Long, erow As Long

Application.ScreenUpdating = False

Worksheets("Swings").Activate
Range("A2:XDH700").ClearContents


'to check the last filled row on sheet named Product Info

lastrow = Worksheets("Product Info").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

If Worksheets("Product Info").Cells(i, 27).Value = "Freestanding > Freestanding Swings, Play" Then

    Worksheets("Product Info").Cells(i, 3).Copy

    erow = Worksheets("Swings").Cells(Rows.Count, 3).End(xlUp).Row

    Worksheets("Product Info").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 2)

    Worksheets("Product Info").Cells(i, 4).Copy

    Worksheets("Product Info").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 3)

    Worksheets("Products").Cells(i, 5).Copy

    Worksheets("Products").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 4)
    
    Worksheets("Products").Cells(i, 8).Copy

    Worksheets("Products").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 5)
    
End If

Next i

Application.ScreenUpdating = True

End Sub

 

[bebo021999]

So, anything relates to row 2 should be 3?

Range("A2:XDH700").ClearContents

become

Range("A3:XDH700").ClearContents

and

For i = 2 To lastrow

become

For i = 3 To lastrow

???

 

[slohman]

So, anything relates to row 2 should be 3?

Range("A2:XDH700").ClearContents

become

Range("A3:XDH700").ClearContents

and

For i = 2 To lastrow

become

For i = 3 To lastrow

???

I'm getting an error on this line

 

[bebo021999]

May be:

 Worksheets("Swings").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 2)

 

[slohman]

May be:

 Worksheets("Swings").Paste Destination:=Worksheets("Swings").Cells(erow + 1, 2)

Sorry obviously didn't make myself clear I'm still getting the merge error and this is the line that turns yellow when I run the vba

 

[bebo021999]

May be not only ClearContents, also ClearFormat (to clear merge cells)?

With Range("A2:XDH700")
   .ClearContents
   .ClearFormats
End with

 

[slohman]

I need A1:H2 to be merged, but nothing else is merged in my worksheet but I'm still getting the error the yellow highlighting is when I debug after the error

 

[slohman]

 

[slohman]

Another problem I have found with the macro it is copying the formulas not values is there a way of doing this.