Copy sheets
 

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

Copy sheets
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message ...
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

Copy sheets
 

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

Alvin



"Ron de Bruin" skrev:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message ...
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

Copy sheets
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message ...
Hi Ron
Well the Workbook i want to get the sheets from is close

Alvin



"Ron de Bruin" skrev:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message
...
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

Copy sheets
 

It's Working Ron

Thanks again
Alvin


"Ron de Bruin" skrev:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message ...
Hi Ron
Well the Workbook i want to get the sheets from is close

Alvin



"Ron de Bruin" skrev:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message
...
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

Copy sheets
 

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message ...
It's Working Ron

Thanks again
Alvin


"Ron de Bruin" skrev:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message
...
Hi Ron
Well the Workbook i want to get the sheets from is close

Alvin



"Ron de Bruin" skrev:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Alvin Hansen" wrote in message
...
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