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

 

[andrewlau2881]

This has worked perfectly, thank you!!

Another thing I am trying to get my head around is the ability to make the script run in all the workbooks in a folder. I was able to do this in Google Sheets but have had no luck in Excel, my organisation is moving everything to Microsoft and now I am struggling to create the same system in excel.

Essentially I have up to a few hundred identical workbooks, all pulling information from a Master, hence the need to hide columns until the Master has been updated.

Each time I update the Master I would like all the other workbooks to unhide the relevant columns. Currently I would have to open each one individually and run the script, it is doable but very time consuming. In Google Sheets I was able to deal with this in two ways:

1. I had a script that ran through all the workbooks in a folder and unhid the relevant columns. This way the end user of the workbook would only ever see the most up to date information and didn't have to do anything other than use the workbook the way I intended them to use it.

2. I had a script that automatically ran when the workbook opened. Whilst this worked well, it did take a long time for the script to run. Up to a couple of minutes at a time.

I haven't found a solution for Option 1 yet in Excel. Not one that I can comprehend.
For option 2 I have tried a few things but none of them seem to run when the workbook is opened.


For me, option 1 would be ideal. I would be in control of when the workbooks updated and would be more aware of any issues before the end user starts usign the workbook themselves.

Is it possible to do this in Excel?

Thank you again for any help you can give me

 

[skillilea]

This is probably a different thread but here is a general approach

1. Hold your values on your workbooks and what you need to do in a table
2. loop through each of them , needs a bunch of error checking. Exists, Is already open, open by who, etc.
3. run your macro on them
4. Close the book saving changes
5. log the success/failure

Example

MrExcel.xlsm
	A	B	C	D	E	F
1	WorkbookName	Path	SheetsToHide	ColumnsToHide	LastRunDate	Message
2	5141.xlsm	C:\ProductionWorkbooks\	sheet1, sheet3	A, B, C	1/24/2024 14:25	C:\ProductionWorkbooks\5141.xlsm :Updated Successfully
3	5142.xlsm	C:\ProductionWorkbooks\	sheet2	D	1/24/2024 14:25	C:\ProductionWorkbooks\5142.xlsm :Updated Successfully
4	5143.xlsm	C:\ProductionWorkbooks\	sheet4,sheet5	F,E,G	1/24/2024 14:25	C:\ProductionWorkbooks\5143.xlsm :Updated Successfully
5	5144.xlsm	C:\ProductionWorkbooks\	sheet2	H,I,A	1/24/2024 14:25	C:\ProductionWorkbooks\5144.xlsm :Updated Successfully
6	5145.xlsm	C:\ProductionWorkbooks\	sheet6	B,C	1/24/2024 14:25	C:\ProductionWorkbooks\5145.xlsm :Updated Successfully
7	5146.xlsm	C:\ProductionWorkbooks\	sheet6	B,C	1/24/2024 14:25	C:\ProductionWorkbooks\5146.xlsm doesn't exist
Sheet9

Macro Starting Point

Public Sub UpdateAllWorkBooksFromMaster()
    Dim tbl As ListObject
    Dim wb As Workbook
    Dim fileName As String, folderPath As String
    Dim sheetsHide As Variant, columnsHide As Variant
    Dim msgRng As Range
    Dim i As Long
    
    On Error GoTo eh
    
    ' get your data table
    Set tbl = ActiveWorkbook.Sheets("Sheet9").ListObjects("tblUpdateWorkbooks")
    
    For i = 1 To tbl.DataBodyRange.Rows.Count
        fileName = tbl.DataBodyRange.Cells(i, 1).Value
        folderPath = tbl.DataBodyRange.Cells(i, 2).Value
        sheetsHide = tbl.DataBodyRange.Cells(i, 3).Value
        columnsHide = tbl.DataBodyRange.Cells(i, 4).Value
        Set msgRng = tbl.DataBodyRange.Cells(i, 6)
        
        ' concat filename
        fileName = folderPath & fileName
        
        ' check if exists
        If Not WorkbookExists(fileName) Then
           Err.Raise 10001, "UpdateAllWorkBooksFromMaster", fileName & " doesn't exist"
        End If
        
        ' open, might want to check if it's already open
        Set wb = Workbooks.Open(fileName)
        wb.Activate 'probably need to activate
        
        ' call your macro(s)
        Call UpdateWorkbookToCorrectSettings(wb, sheetsHide, columnsHide)
        
        wb.Close SaveChanges:=True
        Set wb = Nothing
        
        ' log
        msgRng.Value = fileName & " :Updated Successfully"
        msgRng.Offset(, -1).Value = Now()
    Next
    
    GoTo out
eh:
    msgRng.Value = Err.Description
    msgRng.Offset(, -1).Value = Now()
out:
    If Not wb Is Nothing Then
        wb.Close SaveChanges:=False
    End If
    Set wb = Nothing
End Sub

Private Sub UpdateWorkbookToCorrectSettings(wb As Workbook, sheetsHide As Variant, columnsHide As Variant)
    ' do work here
End Sub

Function WorkbookExists(fullPath As String) As Boolean
    On Error Resume Next
    WorkbookExists = (Len(Dir(fullPath)) > 0)
    On Error GoTo 0
End Function

 

[skillilea]

Maybe we can get this las post moved to a different thread by an awesome moderator

 

[andrewlau2881]

This is probably a different thread but here is a general approach

1. Hold your values on your workbooks and what you need to do in a table
2. loop through each of them , needs a bunch of error checking. Exists, Is already open, open by who, etc.
3. run your macro on them
4. Close the book saving changes
5. log the success/failure

Example

MrExcel.xlsm
	A	B	C	D	E	F
1	WorkbookName	Path	SheetsToHide	ColumnsToHide	LastRunDate	Message
2	5141.xlsm	C:\ProductionWorkbooks\	sheet1, sheet3	A, B, C	1/24/2024 14:25	C:\ProductionWorkbooks\5141.xlsm :Updated Successfully
3	5142.xlsm	C:\ProductionWorkbooks\	sheet2	D	1/24/2024 14:25	C:\ProductionWorkbooks\5142.xlsm :Updated Successfully
4	5143.xlsm	C:\ProductionWorkbooks\	sheet4,sheet5	F,E,G	1/24/2024 14:25	C:\ProductionWorkbooks\5143.xlsm :Updated Successfully
5	5144.xlsm	C:\ProductionWorkbooks\	sheet2	H,I,A	1/24/2024 14:25	C:\ProductionWorkbooks\5144.xlsm :Updated Successfully
6	5145.xlsm	C:\ProductionWorkbooks\	sheet6	B,C	1/24/2024 14:25	C:\ProductionWorkbooks\5145.xlsm :Updated Successfully
7	5146.xlsm	C:\ProductionWorkbooks\	sheet6	B,C	1/24/2024 14:25	C:\ProductionWorkbooks\5146.xlsm doesn't exist
Sheet9

Macro Starting Point

Public Sub UpdateAllWorkBooksFromMaster()
    Dim tbl As ListObject
    Dim wb As Workbook
    Dim fileName As String, folderPath As String
    Dim sheetsHide As Variant, columnsHide As Variant
    Dim msgRng As Range
    Dim i As Long
  
    On Error GoTo eh
  
    ' get your data table
    Set tbl = ActiveWorkbook.Sheets("Sheet9").ListObjects("tblUpdateWorkbooks")
  
    For i = 1 To tbl.DataBodyRange.Rows.Count
        fileName = tbl.DataBodyRange.Cells(i, 1).Value
        folderPath = tbl.DataBodyRange.Cells(i, 2).Value
        sheetsHide = tbl.DataBodyRange.Cells(i, 3).Value
        columnsHide = tbl.DataBodyRange.Cells(i, 4).Value
        Set msgRng = tbl.DataBodyRange.Cells(i, 6)
      
        ' concat filename
        fileName = folderPath & fileName
      
        ' check if exists
        If Not WorkbookExists(fileName) Then
           Err.Raise 10001, "UpdateAllWorkBooksFromMaster", fileName & " doesn't exist"
        End If
      
        ' open, might want to check if it's already open
        Set wb = Workbooks.Open(fileName)
        wb.Activate 'probably need to activate
      
        ' call your macro(s)
        Call UpdateWorkbookToCorrectSettings(wb, sheetsHide, columnsHide)
      
        wb.Close SaveChanges:=True
        Set wb = Nothing
      
        ' log
        msgRng.Value = fileName & " :Updated Successfully"
        msgRng.Offset(, -1).Value = Now()
    Next
  
    GoTo out
eh:
    msgRng.Value = Err.Description
    msgRng.Offset(, -1).Value = Now()
out:
    If Not wb Is Nothing Then
        wb.Close SaveChanges:=False
    End If
    Set wb = Nothing
End Sub

Private Sub UpdateWorkbookToCorrectSettings(wb As Workbook, sheetsHide As Variant, columnsHide As Variant)
    ' do work here
End Sub

Function WorkbookExists(fullPath As String) As Boolean
    On Error Resume Next
    WorkbookExists = (Len(Dir(fullPath)) > 0)
    On Error GoTo 0
End Function

If I am understanding this correctly. This would check the table in 'sheet 9' then hide columns A, B and C ffom sheet 1 and 3 of workbook 5141. Then work through the other named workbooks in the table of sheet9.

I would just like the code from earlier:

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

to run through the named workbooks.


maybe I am misunderstanding all of this, sorry if that is the case.

 

[FormR]

Essentially I have up to a few hundred identical workbooks, all pulling information from a Master, hence the need to hide columns until the Master has been updated.

The whole thing sounds terribly convoluted, maybe an entire re-think is needed? i.e. is Excel (or any spreadsheet) really the right application to use here?

Sub m()

Const path As String = "C:\MyDocuments\" '<<<Change<<<'

Dim f As String
Dim p As Long, i As Long

Application.ScreenUpdating = False

f = Dir(path & "*.xls*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 5 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = ""
                Next p
            End With
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

 

[andrewlau2881]

The whole thing sounds terribly convoluted, maybe an entire re-think is needed? i.e. is Excel (or any spreadsheet) really the right application to use here?

Sub m()

Const path As String = "C:\MyDocuments\" '<<<Change<<<'

Dim f As String
Dim p As Long, i As Long

Application.ScreenUpdating = False

f = Dir(path & "*.xls*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 5 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = ""
                Next p
            End With
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

Thia has worked exactly as intended, the only thing I had to change was ........ "*.xls*" .......... to ........ ".xlsm*"

Out of interest, what other applications would you be thinking of.

For some context, I am a teacher.

I have a master sheet with all of the information about assessments.
Master - Unit 1
Row 1 contains question numbers
Row 2 contains description of questions
Row 3 contains no. of marks for each question


I want each class to be given this information just after they finish each assessment, hence wanting to run the code through the files in a folder (one folder for each class)

There are five units in a year, so I only need to do this five times a year.

I am really open to other ideas about how best to do this.

Thanks again for all of your help, I have made serious progress.

 

[FormR]

Hi, it's difficult to offer any solid suggestions, but "hundreds of near identical" spreadsheets or spreadsheets with "hundreds of tabs" just screams of poor design.

Maybe a database type application (like MS Access) might be better suited, but it's probably quite a steep learning curve.

Thanks again for all of your help, I have made serious progress.