VBA run-time Error 9: subscript out of range

[WeeAlan]

Hi there. I'm currently coming up against a stumbling block with the VBA run-time Error 9 appearing with the following code:

Essentially what I need it to do is open up all worksheets, clear the content of the table, change cells white and colour the 1st column red, except for the worksheet named "Overall".

Debug suggests this line is at fault..."Set tbl = ws.ListObjects(1)"?

Please Login or Register  to view this content.

Many thanks for your help.

[mohadin]

Well
As I can see this error pop up if the sheet has no table in
so ether make sure tables as exist in all sheets Or ...

[WeeAlan]

Thanks mohadin. All sheets have at least 1 table except from "Overall" which I'm asking it to ignore.

[jindon]

Please Login or Register  to view this content.

Is the sheet name correct?
Doesn't actual sheet name have extra space or something?

[WeeAlan]

Hi Jindon. Sheet name is "Overall" without extra spaces or characters.

[jindon]

Then it should work...

When you run the code and encounter the error.
Go to debug mode and just hover the cursor over the ws.Name (not click), so you can actually read the sheet name that raises the error.

[WeeAlan]

Jindon thank you. Never knew you could hover the cursor over so read the sheet raising the error.

It appears that there are hidden sheets, "Blank" and "Control". Now need to figure out how to ignore all 3.

[jindon]

Then perhaps...

Please Login or Register  to view this content.

[WeeAlan]

Fantastic Jindon - hugely appreciated.

[jindon]

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[WeeAlan]

Most certainly does. Thread marked as Solved