Copy sheets

[Guest]

Hi
Is there a way to copy all sheets from one workbook to antother workbook
if i don't know how many sheets there are?


Best regards alvin

 

[Ron de Bruin]

Hi Alvin

If Workbooks("Book2") is open it will copy the worksheets of the activeworkbook to Book2

ActiveWorkbook.Worksheets.Copy after:=Workbooks("Book2").Sheets(Workbooks("Book2").Sheets.Count)

You can also use Sheets.Copy if you have chart sheets also

 

[Guest]

Hi Ron
Well the Workbook i want to get the sheets from is close

Alvin



"Ron de Bruin" skrev:

 

[Ron de Bruin]

Hi Alvin

Try this then

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Worksheets.copy _
after:=Wb1.Sheets(WB1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub

 

[Guest]

It's Working Ron

Thanks again
Alvin


"Ron de Bruin" skrev:

 

[Ron de Bruin]

You are welcome