PDA

View Full Version : Copy used cells and paste to master spreadsheet



CuriousGeorg
09-16-2013, 12:55 AM
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
09-16-2013, 02:24 AM
Workbooks.Open "FILEPATH/User 1.xlsm"
are you sure ? with / , not with \ ?

CuriousGeorg
09-16-2013, 02:30 AM
apologies it is \

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

patel
09-16-2013, 09:11 AM
can you attach sample files ?