Excel VBA Sheets(Array()).Select Subscript out of range

Question

Having a little problem with this tiny bit of code in Excel VBA.

All I'm trying to do is select multiple sheets in a workbook and export them as one PDF. I have many Sheets that I don't want to hide or move around, so earlier in my code I looped and saved the particular names in an array. I was getting this error, so i thought I would resort to simply hard coding their names to see if that worked.

But even with hard coded names i get the 'Subscript out of range error', so there must be something else going wrong.

wb.Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5")).Select

Selection.ExportAsFixedFormat Type:=xlTypePDF, _ ............

Are there any known errors relating to the above statement? (I've only included the first 2 lines as it doesn't execute past the first line) OR are there any suggestions to stop this error occuring?

Note: I have changed the Sheet names as they relate to sensitive data. However an example is "S&C vendor1"

Are you sure your wb reference is pointing to the right workbook? That syntax works fine for me. — Tim Williams
Yep, Set wb = xl.Workbooks.Open(F.Path) I'm also iterating through many workbooks, but i reference the wb in many other parts of my code which execute fine. — drcoding
Then there's likely another piece of information we don't have access to which explains the problem. Try removing the sheet names one-by-one until the error disappears. — Tim Williams
Maybe populate the array with the name of the worksheet directly, instead of manually putting in the names. wb.sheets(1).Name for example for the first worksheet, and so on. — MGP

Gary's Student Gary's Student · Accepted Answer · 2015-11-19T01:01:41

Be sure to spell worksheet names properly:

Sub dural()
   Set wb = ActiveWorkbook
   wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
End Sub

There is no space in the default worksheet name.

Excel VBA Sheets(Array()).Select Subscript out of range

2 Answers