Help with Hiding Columns Macro

[Mobot99]

Hello, need help with a macro that hides columns in multiple worksheets based on whether the drop down menu in that column in one worksheet says "HIDE"

Here is what I have so far, my issue is how do I exclude certain worksheets? Including the worksheet that the macro is in? Or I can just include certain worksheets, whichever is easier.

Sub Hide_Columns_Containing_Value_All_Sheets()

Dim c As Range
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = "Sheet9" Then
End If
For Each c In Range("D4:N4").Cells
If c.Value = "HIDE" Then
c.EntireColumn.Hidden = True
End If
Next c
Next ws
End Sub

 

[Joe4]

Welcome to the Board!

It depends on how many sheets you are including/excluding. If just a few, you can use and AND or OR statement with an IF statement.
If the number of sheets being included is more than a few, and the number of sheets being excluded is more than a few, then one way is to store the names in an array, and iterate through the array with a loop.

If you tell us how many sheets you are including and excluding, we can help you write it out.

 

[Fluff]

Hi & welcome to MrExcel.
How many sheets should it work on & how many should be skipped?

 

[Mobot99]

Novice here, so any help with the actual code would be great, in my above code it's supposed to exclude "sheet9", but that's the only sheet it's applying the macro to. Have heard of arrays, but not sure how to create them.

I have 8 worksheets I am excluding, 12 I am including.

 

[Fluff]

Ok, how about

Sub Hide_Columns_Containing_Value_All_Sheets()
  
   Dim c As Range
   Dim ws As Worksheet
  
   For Each ws In ActiveWorkbook.Worksheets
      Select Case ws.Name
         Case "Sheet9", "Sheet8", "Data", "Macro" ' sheet names to be excluded
         Case Else
            For Each c In Range("D4:N4").Cells
               c.EntireColumn.Hidden = c.Value = "HIDE"
            Next c
      End Select
   Next ws
End Sub

 

[Mobot99]

Thank you, but with that code it's still only applying it to sheet 9 for some reason?

 

[Fluff]

Is the D4:N4 range on one sheet, or each sheet?
If one sheet what is the sheet name?

 

[Mobot99]

Yes on one sheet - on sheet9, is that the issue?

 

[Fluff]

is that the issue?

Partly, partly because I didn't engage the brain.
Try

Sub Hide_Columns_Containing_Value_All_Sheets()
   
   Dim c As Range
   Dim ws As Worksheet
   
   For Each ws In ActiveWorkbook.Worksheets
      Select Case ws.Name
         Case "Sheet9", "Sheet8", "Data", "Macro" ' sheet names to be excluded
         Case Else
            For Each c In Sheets("Sheet9").Range("D4:N4").Cells
               ws.Range(c.Address).EntireColumn.Hidden = c.Value = "HIDE"
            Next c
      End Select
   Next ws
End Sub

 

[Mobot99]

Ah seems close, but I am getting this error "Run-time error '9': Subscript out of Range"