VBA partly copy from workbook x to workbook y

Question

I'm really bad at VBA (kind of understand when reading the code, but I cannot write it myself). I would like to create a report file in workbook y that copies part of the data present in workbook x.

I based my current code on the two following articles: Copy from one workbook and paste into another

and Copy value & offset from workbook X to workbook Y only when range value > 0, then loop for remaining rows

I get no error messages with my code, but it doesn't do anything (except open the files). I double-checked the values and the Like, and they seem ok to me. Any Idea why it doesn't work?

Changed my names & path for anonymity, but here it is:

Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Dim i, LastRow

'## Open both workbooks first:
Set x = Workbooks.Open("C:\filedestination\filex.xlsx")
Set y = Workbooks.Open("C:\filedestination\filey.xlsm")

'Now, copy what I want from x to y:
LastRow = x.Sheets("Sheetname1").Range("A" & Rows.Count).End(xlUp).Row
y.Sheets("Sheetname2").Range("A2:N5000").ClearContents
For i = 2 To LastRow
If x.Sheets("Sheetname1").Cells(i, "D").Value = "Fixedtext" And x.Sheets("Sheetname1").Cells(i, "F").Value Like "*Subject ?ompensation" Then
x.Sheets("Sheetname1").Cells(i, "D").EntireRow.Copy Destination:=y.Sheets("Sheetname2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i

'Close x:
x.Close

End Sub

Thanks

Answer 1

Two things can be wrong:

1. LastRow can be 1, thus it does not enter the loop.
2. Possibly it does not enter in the if clause.

Use the following code to find out which is wrong:

    Private Sub CommandButton1_Click()
    Dim x As Workbook
    Dim y As Workbook
    Dim i As Long, LastRow as Long

    '## Open both workbooks first:
    Set x = Workbooks.Open("C:\filedestination\filex.xlsx")
    Set y = Workbooks.Open("C:\filedestination\filey.xlsm")

    'Now, copy what I want from x to y:
    LastRow = x.Sheets("Sheetname1").Range("A" & Rows.Count).End(xlUp).Row
    debug.print LastRow

    y.Sheets("Sheetname2").Range("A2:N5000").ClearContents
    For i = 2 To LastRow
    If x.Sheets("Sheetname1").Cells(i, "D").Value = "Fixedtext" And x.Sheets("Sheetname1").Cells(i, "F").Value Like "*Subject ?ompensation" Then
    Debug.Print "Entered in the if"
    x.Sheets("Sheetname1").Cells(i, "D").EntireRow.Copy Destination:=y.Sheets("Sheetname2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    else
       Debug.Print "Did not enter for " ;i    
    End If
    Next i

    'Close x:
    x.Close

    End Sub