Rows.Count Error

[Lovelylou79]

Hi Folks,
I am trying to get data from multiple workbooks into 1workbook, building up a large data source.
The code should open each workbook selected, copy cellsA3:AP and last row and then paste into the new workbook at the last blank row.
I am currently struggling with finding the last row to copy to. The code seems correct, and works fine in the individual workbooks, but when using it in the workbook where the data will be stored, it only copies the first 3 rows (A1:AP1 to A3:AP3).
I cant see where it is failing. Any help would be appreciated.

Private Sub CommandButton1_Click()


'Import all User data from the various AM Audit Workbooks
Dim FileNameXLs, f
Dim wb As Workbook, i As Integer
Dim lr As Integer
 
FileNameXLs =Application.GetOpenFilename(filefilter:="Excel Files, *.xl*",MultiSelect:=True)
 
If Not IsArray(FileNameXLs) Then Exit Sub
 
Application.ScreenUpdating = False
 
For Each f In FileNameXLs
Set wb = Workbooks.Open(f)
wb.Worksheets("Sheet1").Range("A3:AP"& Cells(Rows.Count, "B").End(xlUp).Row).CopyThisWorkbook.Sheets("AM_AUDIT_V1").Range("A3")
 
Application.CutCopyMode = False
wb.Close Savechanges:=False
Next f
Application.ScreenUpdating = True

 

End Sub

Thanks

 

[MARK858]

Try

wb.Worksheets("Sheet1").Range("A3:AP"& 
wb.Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).Copy ThisWorkbook.Sheets("AM_AUDIT_V1").Range("A3")

 

[Lovelylou79]

Thanks for the response.
This is still debugging at point of code provided above.

 

[MARK858]

The code didn't compile correctly for me because of the spacing (probably because I typed it on my phone), does the below work for you? if not what does the error say?

wb.Worksheets("Sheet1").Range("A3:AP" & wb.Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).Copy ThisWorkbook.Sheets("AM_AUDIT_V1").Range("A3")

 

[Lovelylou79]

Thanks again Mark, I did assume the line drop wasn’t supposed to be there ?.
The error I’m getting is a run time error 1004 Application-defines or object defined error.

I tried using the code to just select the data, and it only picks up the first 3 rows.
I changed the A3 to A1 and it only picked up the first row.
Can’t say I’ve ever come across this issue before.

 

[MARK858]

No protection or merged cells? (btw the extra spacing was after the &, the line drop was just how the board displayed it)