How to troubleshoot run-time error 9: subscript out of range

[krodriguez]

Hello, I have a code where I'm getting an error on my array ... the code works perfect when array is like this

For Each Ws In Sheets(Array("RLN-Net Realization", "RLN-Red Rev", "RLN-COGS"))

But when I add and extra sheet

For Each Ws In Sheets(Array("RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", WNX-CORP")) I get the error

The name is of the sheet is exactly the same as the one in the code but for some reason the error comes out.

 

[Joe4]

For Each Ws In Sheets(Array("RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", WNX-CORP")) I get the error

You are missing the double-quote before the last value you entered. It should be:
"WNX-CORP"

 

[krodriguez]

Sorry that was a typo...I do have it like that but error comes up

 

[Norie]

Check that all the sheets named in the array are in the active workbook.

Check the sheet names in the array for any misspellings, errant spaces etc.

 

[Fluff]

Select the WNX-CORP sheet & run this

Sub chk()
MsgBox "|" & ActiveSheet.Name & "|"
End Sub

What do you get?

 

[krodriguez]

The error was in one of the sheets name, there was a space right after the sheets's name thus, the code was not reading it correctly.

Issue fixed! thank you

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[krodriguez]

Select the WNX-CORP sheet & run this

Sub chk()
MsgBox "|" & ActiveSheet.Name & "|"
End Sub

What do you get?

Fluff... I asked another question on this forum: VBA code to calculate the size of each sheet in a workbook? and no help so far can you look it up and help me? thanks