Jmorrison67
Board Regular
- Joined
- Aug 20, 2021
- Messages
- 51
- Office Version
- 2016
- Platform
- Windows
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
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