Copy 4 worksheets from workbook A which contains 5 worksheets

0
votes

I need to copy 4 worksheets from workbook A which contains 5 worksheets and create a new workbook B and paste the copied worksheets in the new workbook.

I have done it for one sheet. how can i ameliorate this code :

Sub test()

    ThisWorkbook.Sheets.Copy

    'Saving the new workbook B
    ActiveWorkbook.SaveAs "C:\Users\John\B.xls", FileFormat:=18
End Sub
1
vbaexcel

1 Answers

1
votes

one solution is create the new workbook (B) and then navigate through the worksheets of workbook (A) copying only the worksheets you want.

Sub test()

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = Workbooks.Add
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.name = "<name of worksheet you don't wanto to copy>" Then
            ws.Copy after:=wb.Worksheets(wb.Worksheets.Count)
        End If
    Next

End Sub