Create a new workbook with some of the sheets of the old one

Question

I have a workbook with around 10 sheets, and I would like a macros that creates a new workbook with 4 sheets of the original.

I have this:

Sheets("1").Range("A1:xfd200").Copy
Workbooks.Add
ActiveSheet.Paste Destination:=Range("A1")

But that only creates a new workbook with one of the sheets I need.

I would appreciate any help.

Thanks!!

Possible duplicate of [How to copy sheets to another workbook using vba?](https://stackoverflow.com/questions/6863940/how-to-copy-sheets-to-another-workbook-using-vba) — Alexander, Jul 11 '18 at 17:27

score 0 · Accepted Answer · 2018-07-11T17:02:56.777

Make copies of the other worksheets as well.

'copy first ws to no location to create the workbook then loop through
'and make copies of the others
dim ewb as workbook, w as long
with thisworkbook
    .worksheets(1).copy
    set ewb = activeworkbook
    for w = 2 to 4
        .worksheets(w).copy after:=ewb.worksheets(ewb.worksheets.count)
    next w
    'work with the new workbook as ewb 
end with

'alternately, copy them into a new workbook all at once
Dim wss As Variant, nwb As Workbook

wss = Array("Sheet12", "Sheet13", "Sheet14", "Sheet15")  '<~~ desired worksheets to copy

With ThisWorkbook
    .Worksheets(wss).Copy
    Set nwb = ActiveWorkbook
    'work with the new workbook as nwb
End With

Create a new workbook with some of the sheets of the old one

1 Answers1