Hide column in multiple files in subfolders from folder

Hi Forum,

I've been searching for a VBA solution for a long time and tried multiple threads on the web, but have not been able to make a final solution... So I need help!

I have a bigger Excel "eco system", which contains multiple worksheets in multiple workbooks.

Initial facts:

• The workbooks are placed in subfolders within a main folder
• The main folder is currently placed inside a Sharepoint environment
• The Workbooks could contain multiple sheets to be considered
  - If sheet contains date in cell D2, it should be considered
  - sheets with the name "Log" and "Instruction" should never be considered

What I want the VBA to do:

• An initial inputbox where to enter week number and year.
• Find the column where week number and year matches and hide the column.
• Proceed to next sheet if available or move further on to the next subfolder if available.
• Some sort of error handling / error messaging. "An error occured in file X, sheet X. Would you like to proceed or stop?"
• Possibility to use the VBA even though some of the sheets already have the matching column date hidden.
• Final MsgBox if the VBA succeeded.

Is there anyone out there, that have the time and VBA experience to help me with a proper solution?
Hope my explanation make sense... I would gladly add more info if needed. I've attched a simplfied version of the "eco system" for testing.

BR, Rasmus

Here is another approach for you. Place code in a separate workbook not inside eco system and only run the first sub, which then runs the second sub. This only selects the week, not the year. Let us know if you have questions.

Sub loopAllSubFoldersSelectDirHideWeek()
Dim x As Long
Dim FolderName As String

x = Application.InputBox("Enter the week number you want hidden", , , , , , 1)

With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select the Main folder"
        .AllowMultiSelect = False
        .Show
        FolderName = .SelectedItems(1) & "\"
    End With
'Call LoopAllSubFolders("C:\Users\man\OneDrive\Documents\Excel code\TEST\Disp test\", x) 'Use this line to hard code your path if you want
Call loopAllSubFolders(FolderName, x)
MsgBox "Complete"
End Sub

Sub loopAllSubFolders(ByVal folderPath As String, x As Long)
Dim fileName As String
Dim fullFilePath As String
Dim numFolders As Long
Dim folders() As String
Dim i As Long, m&
Dim ws As Worksheet

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.*", vbDirectory)
While Len(fileName) <> 0
    If Left(fileName, 1) <> "." Then
        fullFilePath = folderPath & fileName
        If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
            ReDim Preserve folders(0 To numFolders) As String
            folders(numFolders) = fullFilePath
            numFolders = numFolders + 1
        Else
            Application.Workbooks.Open (folderPath & fileName)
            For Each ws In ActiveWorkbook.Sheets
                On Error Resume Next
                m = 0
                m = Application.Match("Week " & x, ws.Range("1:1"), 0)
                If m > 0 Then
                    ws.Columns(m).EntireColumn.Hidden = True
                End If
                On Error GoTo 0
            Next
            ActiveWorkbook.Saved = True
            Application.DisplayAlerts = False
                Workbooks(fileName).Close
            Application.DisplayAlerts = True
        End If
    End If
    fileName = Dir()
Wend

For i = 0 To numFolders - 1
    loopAllSubFolders folders(i), x
Next i
End Sub

Maybe one option:

Private Sub DoFolder(Folder)
    Dim SubFolder, File, wb As Workbook, ws As Worksheet, w As Long, d As Long, str As String
    
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    For Each File In Folder.Files
        If Left(Right(File, 5), 4) <> ".xls" Then GoTo nextFile
        Set wb = Workbooks.Open(File)
        For Each ws In wb.Worksheets
            If ws.Name <> "Log" And ws.Name <> "Instruction" Then
                If IsDate(ws.Range("D2")) Then
                    On Error GoTo nextWS
                        w = ws.Rows(1).Find(What:="Week " & wkNum, LookIn:=xlValues, LookAt:=xlWhole).Column
                        d = ws.Rows(2).Find(What:=dte, LookIn:=xlValues, LookAt:=xlWhole).Column
                    On Error GoTo 0
                    If w = d Then
                        ws.Columns(w).Hidden = True
                    End If
                End If
            End If
nextWS:
        Next ws
        wb.Close True
nextFile:
    Next
End Sub