Copy several sheets in the same workbook to new workbook

[srikanthan]

Hi,

I need to copy the contents of the all sheets in a single workbook to a new workbook..

if you could provide me some code, that would be of much use.

thanks,
srikanth

[mudraker]

Make sure the book you are copying from is the active book or replace Set wbAct = ActiveWorkbook
with
Set wbAct = Workbooks("Book1.xls")
change book name as required

Please Login or Register  to view this content.

[srikanthan]

Thanks Mudraker,

But the sheets are added as new sheets in that workbook, would it be possible to update the sheets that are already in that workbook

[Leith Ross]

Hello Srikanthan,

Here is a macro to copy the Workbook. The copy will be saved on the same drive and in the same folder as the ActiveWorkbook.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[srikanthan]

Thanks Leith,

I beleive my query has been misunderstood, to make it clear.. I have a work book say sourcle.xls which has 3 sheets.. I need to copy the contents of all the three sheets in to a single sheet in a separate workbook..

I have framed up a code as below.. can you please help with the code for copying the sheets alone..
NOTE : Sheets should not be added as new one...

Private Sub CommandButton1_Click()
Dim Wbmain As Workbook
Workbooks.Open ("C:\Source.xls")
Workbooks("Source.xls").Activate
Set Wbmain = ActiveWorkbook
Call CopySheets(Wbmain)
End Sub

Sub CopySheets(Wbmain As Workbook)

Dim wS As Worksheet
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
For Each wS In Wbmain.Worksheets
wS.Copy after:=wbNew.Sheets(wbNew.Sheets.Count)
Next wS
End Sub

Above code creates a nwe workbook and copies the contents as new sheets.. I want the new workbook to have single sheet with all the contents of source.xls..

Hope you understand now, sorry for the confusion..