hiding / showing columns...

I've searched online and spent hours trying to make this work, and right now I'm feeling very stupid... can someone please help?

It should be pretty straightforward really, but somehow I keep getting this stupid "Subscript out of range" error.

Here's what I'm trying to do -

There's a sheet called "Main" and 38 other sheets that shows the data (these 38 sheets all have the exact same structure/layout). Let's say these 38 sheets are called "country1", "country2", .... "country38".

Based on user input in cell J10 in "Main", I want to show only the relevant columns in all of the 38 country sheets. Specifically,

If J10 in sheet "Main" = "Option 1", hide all columns in all 38 sheets except columns A to W.

If J10 in sheet "Main" = "Option 2", hide all columns in all 38 sheets except columns Y to AU.

If J10 in sheet "Main" = "Option 3", hide all columns in all 38 sheets except columns AW to BS.

If J10 in sheet "Main" = "Option 4", hide all columns in all 38 sheets except columns BV to CQ.

If J10 sheet "Main = "ShowAll", show all columns in all 38 sheets....




Cell J10 in "Main" will be a drop down bar with the 5 choices.....


Thanks for your help in advance....

Post the code you're using ...

VBA Noob...

Oh wow, that looks so much more elegant than my dummy approach (that didn't work). I basically did a record macro and repeated the code 38 times changing the sheet names.

One thing though, since I have other sheets in the file (calculation worksheetsheets) other than the 38 output sheets (called "country1, country2, etc...), is it possible to ammend your code so that the code only operates on the 38 country sheets instead of every sheet in the file?

Many thanks again.

Typo, Noob:

Please Login or Register  to view this content.

Originally Posted by VBA Noob

Please read forum rules and then wrap the code

VBA Noob

sorry for being such a noob... I actually changed it almost immedaitely after posting but I guess you beat me to it...

I cut and pasted your above (word for word), and I am getting a runtime error code "1004", "Unable to set the Hidden property of the Range class".


Sorry for being such a pain... I really appreciate your help.

Originally Posted by VBA Noob

Unhide all columns on all sheets first and then test it

VBA Noob

Tried that, still getting the same error...

Originally Posted by VBA Noob

Mine seems to work. Post a sample file without data

VBA Noob

I tried to reduce the file size by deleting all data, and eventually deleting most of the 50 sheets. Even with 3 sheets remaining and zipped, I am at 178KB which means I cannot upload the file... is there anyway I can email it or something?