Hi,
I have several worksheets in a workbook and need to merge into one sheet.
I am using
But I need it to collate on a sheet I've already created NOT a new one. Where do I edit the code to do this?Sheets.Add After:=Sheets(Sheets.Count) Dim wks As Worksheet Set wks = Sheets(Sheets.Count) wks.Name = "Sheet3" With Sheets("Sheet1") Dim lastrow As Long lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A1:B" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp) End With With Sheets("Sheet2") lastrow = .Range("B" & .Rows.Count).End(xlUp).Row .Range("A2:B" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1) End With
Thanks
alternatively tell me why
Brings back Error code:Workbooks("Combined1").Sheets("Combined").Cells.ClearContents Workbooks.Open "Amanda.xlsm" Workbooks("Amanda").Sheets("Amanda").Range("A9").CurrentRegion.Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1") Workbooks("Amanda.xlsm").Close SaveChanges:=False Workbooks.Open "Carwyn.xlsm" Workbooks("Carwyn").Sheets("Carwyn").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Carwyn.xlsm").Close SaveChanges:=False Workbooks.Open "Holly.xlsm" Workbooks("Holly").Sheets("Holly").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Holly.xlsm").Close SaveChanges:=False Workbooks.Open "Imran.xlsm" Workbooks("Imran").Sheets("Imran").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Imran.xlsm").Close SaveChanges:=False Workbooks.Open "James.xlsm" Workbooks("James").Sheets("James").Range("A9").CurrentRegion.Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1") Workbooks("James.xlsm").Close SaveChanges:=False Workbooks.Open "Karen.xlsm" Workbooks("Karen").Sheets("Karen").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Karen.xlsm").Close SaveChanges:=False Workbooks.Open "Lauren.xlsm" Workbooks("Lauren").Sheets("Lauren").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Lauren.xlsm").Close SaveChanges:=False Workbooks.Open "Lindsey.xlsm" Workbooks("Lindsey").Sheets("Lindsey").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Lindsey.xlsm").Close SaveChanges:=False Workbooks.Open "Rachael.xlsm" Workbooks("Rachael").Sheets("Rachael").Range("A9").CurrentRegion.Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1") Workbooks("Rachael.xlsm").Close SaveChanges:=False Workbooks.Open "Shafiq.xlsm" Workbooks("Shafiq").Sheets("Shafiq").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Shafiq.xlsm").Close SaveChanges:=False Workbooks.Open "Sharon.xlsm" Workbooks("Sharon").Sheets("Sharon").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("Sharon.xlsm").Close SaveChanges:=False Workbooks.Open "Imran.xlsm" Workbooks("Spare").Sheets("Spare").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("spare.xlsm").Close SaveChanges:=False Workbooks("Spare2").Sheets("Spare2").Range("A9").CurrentRegion.Offset(1).Copy Destination:=Workbooks("Combined1").Sheets("Combined").Range("A1").End(xlDown).Offset(1) Workbooks("spare2.xlsm").Close SaveChanges:=False Sheets("Analysis").Select
Run-Time error '1004':
Application-defined or object-defined error
Thanks in Advance ( I know strictly 2 questions but I had one way of working which had errors so tried alternative that had the same)