Error when I try to hide Multiple Columns from Multiple Sheets

[PVA0724]

Hi

I started working with VBA a couple months ago, so I'm not an expert still in my learning curve. Now, I'm trying to hide multiple columns from the multiple worksheets (same columns for all of them), based on the case condition where 1 = Jan, meaning that it will hide everything except Jan information, 2 = Feb with the same result only showing that particular information. I thougt I had it done...but I'm getting Run time error 1004 Application Defined or Object Defined. I already checked the spelling and also what I got about the syntax...but at least to me everything looks ok...and keep me point me to the line below

Set c = Sheets("Master Control").Range(B2)
See the code below to understand where it is showed up.

Thanks in advance for any help, as I've stuck for almost two days on this.

Sub Hide()
Dim SheetArray As Variant
Dim rng As Range, c As Range
Dim I As Integer

SheetArray = Array("KPI Report Total", "KPI Report 60", "KPI Report 67", "KPI Report 69" _
, "KPI Report 72", "KPI Report 75", "KPI Report 76")

Sheets("Master Control").Activate

For I = 0 To UBound(SheetArray)

Set rng = ActiveWorkbook.Worksheets(SheetArray(I)).Range("B:BW")
Set c = Sheets("Master Control").Range(B2)

Select Case c
Case "1"
Columns("H:BW").Select
Selection.EntireColumn.Hidden = True
End Select
Next I
Sheets("Master Control").Activate
End Sub

 

[njimack]

Welcome to the board.

You're making this too complex - Select Case is used when the variable you're testing could have several values, but in this case, you're simply testing whether or not a single cell = 1. Also, you don't need to select sheets to perform actions on them.. The below code should work for you...

Sub Hide()
Dim SheetArray As Variant
Dim I As Integer

Application.ScreenUpdating = False

SheetArray = Array("KPI Report Total", "KPI Report 60", "KPI Report 67", "KPI Report 69" _
, "KPI Report 72", "KPI Report 75", "KPI Report 76")

For I = 0 To UBound(SheetArray)

    If Sheets("Master Control").Range("B2") = 1 Then Sheets(SheetArray(I)).Columns("H:BW").EntireColumn.Hidden = True

Next I

Application.ScreenUpdating = True

End Sub

 

[PVA0724]

Thanks for your prompt reply, but I showed only the example where the case = 1 then will perform the hide... but actually the case is up to 12 where each number represents a specific period and the hide will be different from that point would you be so kind to point how to use case and if I could do it without selecting the worksheets. Thanks again I have tried this one and it work but I also needed for several conditions.

 

[njimack]

Thanks for your prompt reply, but I showed only the example where the case = 1 then will perform the hide... but actually the case is up to 12 where each number represents a specific period and the hide will be different from that point would you be so kind to point how to use case and if I could do it without selecting the worksheets. Thanks again I have tried this one and it work but I also needed for several conditions.

Something like this (untested)...

Sub Hide()
Dim SheetArray As Variant
Dim I As Integer
Dim c As Integer

Application.ScreenUpdating = False

SheetArray = Array("KPI Report Total", "KPI Report 60", "KPI Report 67", "KPI Report 69" _
, "KPI Report 72", "KPI Report 75", "KPI Report 76")

For I = 0 To UBound(SheetArray)

c = Sheets("Master Control").Range("B2")

Select Case c

    Case 1: Sheets(SheetArray(I)).Columns("H:BW").EntireColumn.Hidden = True
    Case 2: 'code to hide different sheets
    Case 3: 'code to hide different sheets
    Case 4 'etc
End Select

Next I

Application.ScreenUpdating = True

End Sub

 

[andrewkard]

Sub Hide()
Dim SheetArray As Variant
Dim rng As Range, c As Range
Dim I As Integer
SheetArray = Array("KPI Report Total", "KPI Report 60", "KPI Report 67", "KPI Report 69" _
, "KPI Report 72", "KPI Report 75", "KPI Report 76")

Set c = Sheets("Master Control").Range("B2")
Select Case c
Case "1"
For I = 0 To UBound(SheetArray)

Worksheets(SheetArray(I)).Columns("H:BW").EntireColumn.Hidden = True

Next I
End Select
Sheets("Master Control").Activate
End Sub