I need the same code to run on sheets 5 - 100 of my workbook

[andrewlau2881]

Hi all,

I have sheets which are updated from a master for hundreds of people in my organisation. When I update the master I would like all the other sheets to recognise this and unhide columns which have now been populated from the master. The VBA code I have works on the sheet that is open. However, I would like it to then run on sheet 5-100 of that same workbook. I have tried piecing together something from various sources but can't get anything to work fully.

Sub Workbook_Open()

Dim p As Integer
For p = 2 To 31
If Cells(1, p).Value = "" Then
Columns(p).Hidden = True
Else: Columns(p).Hidden = False
End If
Next p

End Sub

 

[skillilea]

This should work

Private Sub Workbook_Open()
    Dim i As Integer
    Dim ws As Worksheet
  
    Application.EnableEvents = False
    Application.ScreenUpdating = False
  
    For Each ws In ActiveWorkbook.Sheets
        For i = 2 To 31
            If ws.Cells(1, i).Value = "" Then
                ws.Columns(i).Hidden = True
            Else
                ws.Columns(i).Hidden = False
            End If
        Next
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I would make the 2 to 31 a range, probably a sheet named range incase it changes but this should get you started

 

[FormR]

I would like it to then run on sheet 5-100 of that same workbook.

Hi, welcome to the forum. If you are referring to the position of the sheets in the workbook, i.e. starting from the 5th tab along in the list of sheet tabs, then here is another option you can try.

Sub Workbook_Open()
Dim p As Long, i As Long
For i = 5 To 100
    With ThisWorkbook.Sheets(i)
        For p = 2 To 31
            .Columns(p).Hidden = .Cells(1, p).Value = ""
        Next p
    End With
Next i
End Sub

 

[skillilea]

This should work

Private Sub Workbook_Open()
    Dim i As Integer
    Dim ws As Worksheet
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    For Each ws In ActiveWorkbook.Sheets
        For i = 2 To 31
            If ws.Cells(1, i).Value = "" Then
                ws.Columns(i).Hidden = True
            Else
                ws.Columns(i).Hidden = False
            End If
        Next
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I would make the 2 to 31 a range, probably a sheet named range incase it changes but this should get you started

Yep, missed that you need to wrap the code in this: If ws.Index >= 5 And ws.Index <= 100 Then....

 

[andrewlau2881]

This should work

Private Sub Workbook_Open()
    Dim i As Integer
    Dim ws As Worksheet
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    For Each ws In ActiveWorkbook.Sheets
        For i = 2 To 31
            If ws.Cells(1, i).Value = "" Then
                ws.Columns(i).Hidden = True
            Else
                ws.Columns(i).Hidden = False
            End If
        Next
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I would make the 2 to 31 a range, probably a sheet named range incase it changes but this should get you started

Thank you, this works perfectly!!

As I understand it, this goes through every sheet in the workbook. I would like it to only run in sheets 5-100 of the workbook i.e. miss out the first 4 sheets which have information that is never going to change.

I understand I could just populate cells(1,i) with something in the first four sheets of the workbook and hide the first row, but I would prefer the code to only miss out these first four sheets.

Once again, thank you. So much to learn with VBA

 

[skillilea]

This should do it

Public Sub HideColumns()

    Dim i As Integer
    Dim ws As Worksheet
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Index >= 5 And ws.Index <= 100 Then
            For i = 2 To 31
                ws.Columns(i).Hidden = ws.Cells(1, i).Value = ""
            Next
        End If
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[andrewlau2881]

This should do it

Public Sub HideColumns()

    Dim i As Integer
    Dim ws As Worksheet
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    For Each ws In ActiveWorkbook.Sheets
        If ws.Index >= 5 And ws.Index <= 100 Then
            For i = 2 To 31
                ws.Columns(i).Hidden = ws.Cells(1, i).Value = ""
            Next
        End If
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This has worked perfectly, thank you!!

 

[FormR]

Hi, out of interest, did you also try the code in post #3?

 

[andrewlau2881]

Hi, out of interest, did you also try the code in post #3?

I didn't, but will try it and let you know. Thanks

 

[andrewlau2881]

I didn't, but will try it and let you know. Thanks

Yes, it worked exactly as I need it to.

Thank you for taking the time to respond to my question. I appreciate it a lot.