CuriousGeorg
09-02-2013, 05:01 AM
Hi,
I have several worksheets in a workbook and need to merge into one sheet.
I am using
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
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?
Thanks
:banghead:
alternatively tell me why
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
Brings back Error code:
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)
I have several worksheets in a workbook and need to merge into one sheet.
I am using
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
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?
Thanks
:banghead:
alternatively tell me why
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
Brings back Error code:
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)