VBA EXCEL: Fixing “Runtime-error '9'”: Subscript out of range

[krodriguez]

Hello,

I have the below code which attempts to select several sheets in a wordbook and save them on a separate file, but I'm getting the “Runtime-error '9'”: Subscript out of range, can someone assist to fix it? Thanks

Sub SaveSheetsAsFiles()
Dim SheetsToSave
SheetsToSave = Array("Rainbow", "RLN-Net Realization RLN-Red Rev RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
sht.Copy
'file location is same as workbook the code is in. Change to suit
'file format is .xlsm - change to suit
ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub

 

[silentwolf]

Hi,
that means that the array can not store that amount of values... and that is as you did not declare the array in the first place..

dim arrSheets() as variant

then you need to redim it so it can hold the array or you declare a fixed size array(0 to 6) in your case ..

 

[krodriguez]

Thanks, newbie on this so I am not following you. how should the code look then?

 

[silentwolf]

You need to declare a variable for the array

Sub SaveSheetAsFieles()
dim SheetToSave(0 to 6)as variant

if you only have 7 different sheet names or not more.. if you need more you would to change it to (0 to 10) holds 11 arrays ...

then it should work try it out...

 

[Fluff]

The error is telling you that one or more of the sheets don't exist.
For instance is this "RLN-Net Realization RLN-Red Rev RLN-COGS" 3 different sheets (its too long to be a single sheet name)?
If so it should be "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS"

 

[Fluff]

@silentwolf
When assigning values to array like the op is doing you do not need to specify the size of the array.
There is nothing wrong with the code except the sheet names have not been properly entered.

 

[silentwolf]

Hi Fluff,

ok thanks for the clarifying! Sorry that I made a wrong valueation of the problem! Ups But luckely you put him on the right track!!

 

[krodriguez]

Correct! I fixed it and now works. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback