VBA to copy multiple worksheets within a workbook and export to a new workbook and save it

[Jmorrison67]

Good afternoon MrExcel community,

I am currently looking for some help in adding multiple sheets into this code. It worked OK with one tab/worksheet added but when i try to add in multiple sheet names I get the 'Subscript out of range' error.

This is what currently works:

Sub test()
Sheets("Cover").Copy
ActiveWorkbook.SaveAs Filename:="c:\users\morrisonj\TEST22" & Format(Date, "ddmmyyyy") & ".xlsx"
End Sub

Output >> which is a new workbook with one tab called Cover

What I am trying to do is add more worksheets to run off at the same time. Worksheets names are:
Cover
Contents
Total Costs Summary
Customer Services - KPI's


So I tried researching on the forum and online and this code came up (amended to input my sheet names):

Sub test()
Sheets(Array("Cover", "Contents", "Total Costs Summary", "Customer Services - KPI's")).Copy
ActiveWorkbook.SaveAs Filename:="c:\users\morrisonj\TEST23" & Format(Date, "ddmmyyyy") & ".xlsx"
End Sub

I then get this error:

Debug:

My ultimate goal is to press a button to run off an extract from a workbook which has 80+ tabs and extract 4 tabs for one manager, 4 for another etc so aim to repeat the code by changing the sheet names.

Any help would be appreciated

Kind regards
Jmorrison67

 

[Fluff]

Check the spelling of the sheet names, especially check for any leading/trailing spaces.

 

[Jmorrison67]

Check the spelling of the sheet names, especially check for any leading/trailing spaces.

As simple as that - 'Contents' had a space after it!!
Works a treat now

 

[Fluff]

Glad to help & thanks for the feedback.