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
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