Hi all,
I hope I get this right as it's my first post. I am trying to loop through a list of sheets. In each sheet I want a range of cells to be assesed and then the columns hidden based on the cell value. I can get this macro to work as long it is for one sheet. As soon as I extend the loop to look at more than one sheet, I get an error message. It's the first time I have used the Union function, something I am not used to. Any help would be most welcome, I've been looking at this for ages!
Sub HideColumns()
Dim ColumnsToHide As Range
Dim SelectedCells As Range
Dim Cell As Object
Dim Item As Range
Dim TeamTab As Range
Set TeamTab = Sheets("Options").Range("D9:D10") '"TeamTabs"
'Set ColumnsToHide = Range("P4:AA4")
For Each Item In TeamTab
'On Error Resume Next
With Sheets(Item.Value).Select
Sheets(Item.Value).Range("P4:AA4").EntireColumn.Hidden = False 'ColumnsToHide.
For Each Cell In Sheets(Item.Value).Range("P4:AA4")
If Cell.Value = 1 Then
If SelectedCells Is Nothing Then
Set SelectedCells = Range(Cell.Address)
Else: Set SelectedCells = Union(SelectedCells, Range(Cell.Address))
End If
End If
Next
If SelectedCells Is Nothing Then
Sheets(Item.Value).Range("P4:AA4").EntireColumn.Hidden = False
Else: SelectedCells.EntireColumn.Hidden = True
End If
End With
Next Item
End Sub
Bookmarks