Copy used cells and paste to master spreadsheet

[CuriousGeorg]

Another day another query, good morning!

I have a code which appeared to work and not sure why it doesn't now.

Basically I have users with individual workbooks filling in information. (from A:T). I then have a master spreadsheet that is supposed to collate all the information onto one sheet so that I can run some stats from this.

However, I get an error and the whole thing falls down.



What is the most efficient code to get this working. (bearing in mind not each user will have the same amount of rows to paste and there ARE named ranges in there)

Sub combine()Workbooks("Tracker").Sheets("Combined").Cells.ClearContents


Workbooks.Open "FILEPATH/User 1.xlsm"
Workbooks("User 1").Sheets("User 1").Range("A8").CurrentRegion.Offset(1, 0).Copy Destination:=Workbooks("Tracker").Sheets("Combined").Range("A1").End(xlDown).Offset(1, 0)
Workbooks("User 1.xlsm").Close SaveChanges:=False


Workbooks.Open "FILEPATH/User 2.xlsm"
Workbooks("User 2").Sheets("User 2").Range("A8").CurrentRegion.Offset(1, 0).Copy Destination:=Workbooks("Tracker").Sheets("Combined").Range("A1").End(xlDown).Offset(1, 0)
Workbooks("User 2.xlsm").Close SaveChanges:=False
Sheets("Analysis").Activate
End Sub

Error i get is:

1004 run-time error and it highlights

Workbooks("User 1").Sheets("User 1").Range("A8").CurrentRegion.Offset(1, 0).Copy Destination:=Workbooks("Tracker").Sheets("Combined").Range("A1").End(xlDown).Offset(1, 0)

The headings on User 1 start on row 8.

Mind blown

[patel]

Workbooks.Open "FILEPATH/User 1.xlsm" 

are you sure ? with / , not with \ ?

[CuriousGeorg]

apologies it is \

typo when hiding the real filename. the file opens fine. I believe its the "copy paste" bit thats not working.

[patel]

can you attach sample files ?