[SOLVED] Copy sheets

[Alvin Hansen]

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

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



"Alvin Hansen" <AlvinHansen@discussions.microsoft.com> wrote in message news:84CC7092-1F57-4458-9F8F-BA0075BC3932@microsoft.com...
> 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
>

[Alvin Hansen]

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" <AlvinHansen@discussions.microsoft.com> wrote in message news:84CC7092-1F57-4458-9F8F-BA0075BC3932@microsoft.com...
> > 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

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" <AlvinHansen@discussions.microsoft.com> wrote in message news:8BEC8428-4385-487E-9EB4-86896C88642D@microsoft.com...
> 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" <AlvinHansen@discussions.microsoft.com> wrote in message
>> news:84CC7092-1F57-4458-9F8F-BA0075BC3932@microsoft.com...
>> > 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
>> >
>>
>>
>>

[Alvin Hansen]

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" <AlvinHansen@discussions.microsoft.com> wrote in message news:8BEC8428-4385-487E-9EB4-86896C88642D@microsoft.com...
> > 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" <AlvinHansen@discussions.microsoft.com> wrote in message
> >> news:84CC7092-1F57-4458-9F8F-BA0075BC3932@microsoft.com...
> >> > 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]

You are welcome

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



"Alvin Hansen" <AlvinHansen@discussions.microsoft.com> wrote in message news:62AECF6D-6584-492D-BDFF-0473C70E29D2@microsoft.com...
> 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" <AlvinHansen@discussions.microsoft.com> wrote in message
>> news:8BEC8428-4385-487E-9EB4-86896C88642D@microsoft.com...
>> > 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" <AlvinHansen@discussions.microsoft.com> wrote in message
>> >> news:84CC7092-1F57-4458-9F8F-BA0075BC3932@microsoft.com...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>