hiding / showing columns...

**LSB M** · 07-07-2008, 01:57 PM

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....

**shg** · 07-07-2008, 02:25 PM

Post the code you're using ...

**royUK** · 07-07-2008, 02:31 PM

Try setting up Custom Views. Then use a WorkSheet_Change event to hide/unhide the views based on the selection in J10. The basic code for this can be recorded.

**VBA Noob** · 07-07-2008, 02:31 PM

Note: Your requests will hide J10 in Main sheet also so you will lose the control.

Right click sheet tab > select View code > paste in the below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Wsht As Worksheet
If Target.Address(0, 0) = "J10" Then

    For Each Wsht In Worksheets
        With Wsht
            .Columns("A:IV").EntireColumn.Hidden = False
            Select Case LCase(Target.Value)
                Case "option 1"
                    .Columns("AW:IV").EntireColumn.Hidden = True
                    Case "option 2"
                    .Range("A:Y,AV:IV").EntireColumn.Hidden = True
                    Case "option 3"
                    .Range("A:AR,BR:IV").EntireColumn.Hidden = True
                    Case "option 4"
                    .Range("A:BU,CR:IV").EntireColumn.Hidden = True
                    Case "showAll"
                    .Columns("A:IV").EntireColumn.Hidden = False
                End Select
        End With
 Next Wsht
End If


End Sub

VBA Noob

**LSB M** · 07-07-2008, 03:25 PM

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.

**VBA Noob** · 07-07-2008, 03:30 PM

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Wsht As Worksheet
If Target.Address(0, 0) = "J10" Then

    For Each Wsht In Worksheets
        If LCase(Wsht.Name) Like "*country" Then
        With Wsht
            .Columns("A:IV").EntireColumn.Hidden = False
            Select Case LCase(Target.Value)
                Case "option 1"
                    .Columns("AW:IV").EntireColumn.Hidden = True
                    Case "option 2"
                    .Range("A:Y,AV:IV").EntireColumn.Hidden = True
                    Case "option 3"
                    .Range("A:AR,BR:IV").EntireColumn.Hidden = True
                    Case "option 4"
                    .Range("A:BU,CR:IV").EntireColumn.Hidden = True
                    Case "showAll"
                    .Columns("A:IV").EntireColumn.Hidden = False
                End Select
        End With
        End If
    Next Wsht
End If


End Sub

VBA Noob

**shg** · 07-07-2008, 03:36 PM

Typo, Noob:

If LCase(Wsht.Name) Like "country*" Then

**LSB M** · 07-07-2008, 04:05 PM

OK, so I copied your code and changed some of the names to match the actual sheet names.... When my cell J10 is changed, the hiding of columns should happen automatically, right? Well, nothing is happening... I'm wondering what I'm not doing right.....

By the way, the country output sheets are actually called "AMR Sales", "US Sales", "BR Sales", "MX Sales", "EUR Sales", "DE Sales", etc. etc....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Wsht As Worksheet
If Target.Address(0, 0) = "J10" Then

    For Each Wsht In Worksheets
        If LCase(Wsht.Name) Like "*Sales" Then
        With Wsht
            .Columns("A:IV").EntireColumn.Hidden = False
            Select Case LCase(Target.Value)
                Case "Option 1"
                    .Columns("X:IV").EntireColumn.Hidden = True
                    Case "Option 2"
                    .Range("A:X,AV:IV").EntireColumn.Hidden = True
                    Case "Option 3"
                    .Range("A:AV,BT:IV").EntireColumn.Hidden = True
                    Case "Option 4"
                    .Range("A:BT,CR:IV").EntireColumn.Hidden = True
                    Case "Show All"
                    .Columns("A:IV").EntireColumn.Hidden = False
                End Select
        End With
        End If
    Next Wsht
End If


End Sub