Mass Sheet Update

[Exhale180]

I never know how to start these threads. I guess I will start with what I currently have working and what I want it to also be able to do.
I have a Master workbook that used to have one sheet (Sheet1). That one sheet would update the sheet of the same name in all workbooks in a designated directory. So all workbooks in said directory with a sheet named "Sheet1" would basically match the master sheet. It worked great. I just clicked the custom button and poof. However I have split this one sheet into 3 separate sheets within the Master Workbook.
For an example the old Workbook would update all sheets named ("Sheet1") in all workbooks that had said named sheet. Now I would like to only update all sheets that share the same name as the active sheet (Sheet1, Sheet2, Sheet3 etc.) in all the Workbooks that have a sheet that shares that name. Is this possible?

 

[mumps]

Please use code tags to post the code you are currently using.

 

[Exhale180]

Please use code tags to post the code you are currently using.

Option Explicit


' ----------------------------------------------------------------
' Procedure Name: AddNames
' Purpose: Add Name for Tool Description Cells.
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter pwsToolsWorksheet (Worksheet): The worksheet into which name is added.
' Author: Jim
' Date: 10/4/2022
' ----------------------------------------------------------------

Sub AddNames(ByRef pwsToolsWorksheet As Worksheet)
   
'   parameter pwsToolsWorksheet is the worksheet into which names are added.

'   Set Active Worksheet
    Worksheets(ActiveSheet.Name).Activate
   
'   Cell containing the header Tool Description
    Dim rCellFound As Range
   
'   Cells containing Tool Descriptions.
    Dim rDataCells As Range
   
'   The row of the last entry in the Tool Descriptions data.
    Dim iLastCellRow As Long
   
'   The column of the last header in the Tool Descriptions data.
    Dim iLastColumn As Long
           
'   Look for the text Tool Description in the worksheet
    On Error Resume Next
    Set rCellFound = pwsToolsWorksheet.Cells.Find(What:="Tool Description", LookAt:=xlWhole)
    On Error GoTo 0
   
'   If the header text "Tool Description" is not found then exit sub
    If rCellFound Is Nothing Then Exit Sub

    With rCellFound
 
'       Add name for the tool description header.
        pwsToolsWorksheet.Parent.Names.Add Name:="Header_ToolDescriptions", RefersTo:="=" & pwsToolsWorksheet.Name & "!" & .Address
 
'       Get the row of last cell containing a tool description.
        iLastCellRow = .Offset(100000).End(xlUp).Row
       
'       Set range object to point to all Tool Description data cells.
        Set rDataCells = .Offset(1).Resize(iLastCellRow - 1)
       
'       Add name to include all tool description data cells.
        pwsToolsWorksheet.Parent.Names.Add Name:="ToolDescriptions", RefersTo:="=" & pwsToolsWorksheet.Name & "!" & rDataCells.Address
       
        iLastColumn = .End(xlToRight).Column

'       Set range object to point to all Tool Description data cells.
        Set rDataCells = .Offset(1).Resize(iLastCellRow - 1, iLastColumn)
       
'       Add name to include all tool description data cells.
        pwsToolsWorksheet.Parent.Names.Add Name:="ToolData", RefersTo:="=" & pwsToolsWorksheet.Name & "!" & rDataCells.Address
       
'       Set range object to point to all Tool Index cells.
'        Set rDataCells = .Offset(1, -1).Resize(iLastCellRow - 1)
       
'       Add name to include all tool index cells.
'        pwsToolsWorksheet.Parent.Names.Add Name:="ToolsIndexes", RefersTo:="=" & pwsToolsWorksheet.Name & "!" & rDataCells.Address
       
    End With
   
End Sub

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: CopyPasteToolings
' Purpose: Update various files' Tooling data worksheet.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 10/3/2022
' ----------------------------------------------------------------

Sub CopyPasteToolings()

'   Workbook object for Target workbook
    Dim wbTarget As Workbook

'   Worksheet object for the Tooling worksheet in the Master workbook.
    Dim wsMasterTooling As Worksheet
   
'   Worksheet object for the just opened workbook.
    Dim wsTargetTooling As Worksheet
       
'   Array holding folder names.
    Dim asFolders() As String
   
'   This is the name of the template file to update.
'   Dim sTemplateFileName As String
   
'   Count of folders to process.
    Dim iFoldersCount As Long
   
'   Used to loop through folders.
    Dim iFolder As Long
   
'   Count of files in folder.
    Dim iFilesCount As Long
   
    Dim iFoldersAbove As Long
   
'   Count of files in folder.
    Dim iWorkbooksCount As Long
   
'   Folder containing the source and target folders.
    Dim sBasePath As String
   
'   Full path plus filename
    Dim sFileSpec As String
   
'   Used to store the list of files after from the Dir function.
    Dim vFiles As Variant
   
'   Name of template file to process.
'   sTemplateFileName = "Setup Sheet Template.xlsx"
   
'   ------------------------------------------------------------
'          Specify folders containing workbooks to update
'   ------------------------------------------------------------

'   Specify the number of folders to process.
    iFoldersCount = 2

'   Resize array to accommodate all folder names.
    ReDim asFolders(iFoldersCount) '<< set the Redim value to the number of folders to process
   
'   Load array with folder names. IDEALLY these are from values in a worksheet. That
'   way you do not have to access this code to change the folders to be accessed.
'
    asFolders(1) = "Milling"
    asFolders(2) = "Turning"
   
    Application.ScreenUpdating = False
   
'   Master workbook has a worksheet named Tooling. Set the wsMasterTooling
'   worksheet object to point to that Tooling worksheet.
    Set wsMasterTooling = ThisWorkbook.Worksheets(ActiveSheet.Name)
   
'   How many folders above the one for this file are the folders containing data files?
    iFoldersAbove = 0
   
'   ----------------------------------
'          Setup The sBasePath
'   ----------------------------------
   
'   Master is in the same folder as the folders containg worksheets to update.
    If iFoldersAbove = 0 _
     Then
'       Get the base path for all folders being processed if they are in the same folder as this workbook.
        sBasePath = ThisWorkbook.Path & "\"
   
'   Target file folders are located in the folder one above this file's folder.
    ElseIf iFoldersAbove = 1 _
     Then
'       Get the base path for all folders being processed if they are in a folder that is 1 folder up.
        sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"
   
'   Target file folders are located in the folder two above this file's folder.
    ElseIf iFoldersAbove = 2 _
     Then
'       Get folder one above where this file is located.
        sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)
       
'       Get folder two above the one where this file is located.
        sBasePath = Left(sBasePath, InStrRev(sBasePath, "\") - 1) & "\"
   
    End If
   
'   -------------------------------------
'          Iterate through folders
'   -------------------------------------

'   Iterate through the folders containing the data files.
    For iFolder = 1 To iFoldersCount
       
        vFiles = Dir(sBasePath & asFolders(iFolder) & "\")
       
'       -------------------------------------
'              Iterate through files
'       -------------------------------------
       
        While (vFiles <> "")
       
            iFilesCount = iFilesCount + 1

'           This is the full "file specification" with full path and filename.
            sFileSpec = sBasePath & asFolders(iFolder) & "\" & vFiles

            Set wbTarget = Nothing
           
'           Point the wbTarget object to the workbook that is opened.
'           If the file being processed is not an Excel workbook then
'           this would cause an error.
            On Error Resume Next
            Set wbTarget = Workbooks.Open(sFileSpec)
            On Error GoTo 0
           
'           Exclude pdf files which WILL open in Excel
            If Right(sFileSpec, 3) = "pdf" _
             Then
                Application.DisplayAlerts = False
                wbTarget.Close
                Application.DisplayAlerts = True
               
'           Check that the workbook exists before processing the file.
            ElseIf Not wbTarget Is Nothing _
             Then
           
                With wbTarget

                    Set wsTargetTooling = Nothing
                   
'                   Point the wsTargetTooling object to the Tooling worksheet.
'                   If the workbook being processed does not contain the specified
'                   worksheet (Tooling) then this would cause an error.
                    On Error Resume Next
                    Set wsTargetTooling = wbTarget.Worksheets("Tooling")
                    On Error GoTo 0
                   
'                   Check that the worksheet exists before processing the file.
                    If Not wsTargetTooling Is Nothing _
                     Then

'                       ------------------------------------------------
'                               Call sub that does the updates.
'                       ------------------------------------------------
                        Call UpdateFile(wsMasterTooling, wsTargetTooling)
                       
                        iWorkbooksCount = iWorkbooksCount + 1
                       
                    End If 'Not wsTargetTooling Is Nothing
                   
'                   Close the file, save changes
                    .Close SaveChanges:=True

                End With 'wbTarget
           
            End If 'Not wbTarget Is Nothing
           
            vFiles = Dir
       
        Wend
           
    Next iFolder
   
'   --------------------------------------------------
'           Update file Setup Sheet Template
'   --------------------------------------------------

'    On Error Resume Next
'    Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & sTemplateFileName)
'    On Error GoTo 0

'    If Not wbTarget Is Nothing _
'     Then
                   
'        With wbTarget
'            On Error Resume Next
'            Set wsTargetTooling = .Worksheets("Tooling")
'            On Error GoTo 0
           
'           Do the update of the workbook, call sub that does it.
'            If Not wsTargetTooling Is Nothing _
'             Then
'                Call UpdateFile(wsMasterTooling, wsTargetTooling)
'            End If
       
'           Close the file, save changes
'            .Close SaveChanges:=True
           
'           increment files processed count and workbooks updated count
'            iFilesCount = iFilesCount + 1
'            iWorkbooksCount = iWorkbooksCount + 1
'        End With
       
'    End If

'   -----------------------
'           Closeout
'   -----------------------

    Application.CutCopyMode = False
   
    MsgBox "Done processing " & iFilesCount & " files." & Chr(10) _
         & iWorkbooksCount & " were Tooling workbooks", _
    vbInformation, _
    "Updating files with Toolings data"
   
End Sub

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: CopyPasteToolings
' Purpose: Update various files' Tooling data worksheet.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 10/3/2022
' ----------------------------------------------------------------

Sub CopyPasteToolings()

'   Workbook object for Target workbook
    Dim wbTarget As Workbook

'   Worksheet object for the Tooling worksheet in the Master workbook.
    Dim wsMasterTooling As Worksheet
   
'   Worksheet object for the just opened workbook.
    Dim wsTargetTooling As Worksheet
       
'   Array holding folder names.
    Dim asFolders() As String
   
'   This is the name of the template file to update.
'   Dim sTemplateFileName As String
   
'   Count of folders to process.
    Dim iFoldersCount As Long
   
'   Used to loop through folders.
    Dim iFolder As Long
   
'   Count of files in folder.
    Dim iFilesCount As Long
   
    Dim iFoldersAbove As Long
   
'   Count of files in folder.
    Dim iWorkbooksCount As Long
   
'   Folder containing the source and target folders.
    Dim sBasePath As String
   
'   Full path plus filename
    Dim sFileSpec As String
   
'   Used to store the list of files after from the Dir function.
    Dim vFiles As Variant
   
'   Name of template file to process.
'   sTemplateFileName = "Setup Sheet Template.xlsx"
   
'   ------------------------------------------------------------
'          Specify folders containing workbooks to update
'   ------------------------------------------------------------

'   Specify the number of folders to process.
    iFoldersCount = 2

'   Resize array to accommodate all folder names.
    ReDim asFolders(iFoldersCount) '<< set the Redim value to the number of folders to process
   
'   Load array with folder names. IDEALLY these are from values in a worksheet. That
'   way you do not have to access this code to change the folders to be accessed.
'
    asFolders(1) = "Milling"
    asFolders(2) = "Turning"
   
    Application.ScreenUpdating = False
   
'   Master workbook has a worksheet named Tooling. Set the wsMasterTooling
'   worksheet object to point to that Tooling worksheet.
    Set wsMasterTooling = ThisWorkbook.Worksheets(ActiveSheet.Name)
   
'   How many folders above the one for this file are the folders containing data files?
    iFoldersAbove = 0
   
'   ----------------------------------
'          Setup The sBasePath
'   ----------------------------------
   
'   Master is in the same folder as the folders containg worksheets to update.
    If iFoldersAbove = 0 _
     Then
'       Get the base path for all folders being processed if they are in the same folder as this workbook.
        sBasePath = ThisWorkbook.Path & "\"
   
'   Target file folders are located in the folder one above this file's folder.
    ElseIf iFoldersAbove = 1 _
     Then
'       Get the base path for all folders being processed if they are in a folder that is 1 folder up.
        sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"
   
'   Target file folders are located in the folder two above this file's folder.
    ElseIf iFoldersAbove = 2 _
     Then
'       Get folder one above where this file is located.
        sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)
       
'       Get folder two above the one where this file is located.
        sBasePath = Left(sBasePath, InStrRev(sBasePath, "\") - 1) & "\"
   
    End If
   
'   -------------------------------------
'          Iterate through folders
'   -------------------------------------

'   Iterate through the folders containing the data files.
    For iFolder = 1 To iFoldersCount
       
        vFiles = Dir(sBasePath & asFolders(iFolder) & "\")
       
'       -------------------------------------
'              Iterate through files
'       -------------------------------------
       
        While (vFiles <> "")
       
            iFilesCount = iFilesCount + 1

'           This is the full "file specification" with full path and filename.
            sFileSpec = sBasePath & asFolders(iFolder) & "\" & vFiles

            Set wbTarget = Nothing
           
'           Point the wbTarget object to the workbook that is opened.
'           If the file being processed is not an Excel workbook then
'           this would cause an error.
            On Error Resume Next
            Set wbTarget = Workbooks.Open(sFileSpec)
            On Error GoTo 0
           
'           Exclude pdf files which WILL open in Excel
            If Right(sFileSpec, 3) = "pdf" _
             Then
                Application.DisplayAlerts = False
                wbTarget.Close
                Application.DisplayAlerts = True
               
'           Check that the workbook exists before processing the file.
            ElseIf Not wbTarget Is Nothing _
             Then
           
                With wbTarget

                    Set wsTargetTooling = Nothing
                   
'                   Point the wsTargetTooling object to the Tooling worksheet.
'                   If the workbook being processed does not contain the specified
'                   worksheet (Tooling) then this would cause an error.
                    On Error Resume Next
                    Set wsTargetTooling = wbTarget.Worksheets("Tooling")
                    On Error GoTo 0
                   
'                   Check that the worksheet exists before processing the file.
                    If Not wsTargetTooling Is Nothing _
                     Then

'                       ------------------------------------------------
'                               Call sub that does the updates.
'                       ------------------------------------------------
                        Call UpdateFile(wsMasterTooling, wsTargetTooling)
                       
                        iWorkbooksCount = iWorkbooksCount + 1
                       
                    End If 'Not wsTargetTooling Is Nothing
                   
'                   Close the file, save changes
                    .Close SaveChanges:=True

                End With 'wbTarget
           
            End If 'Not wbTarget Is Nothing
           
            vFiles = Dir
       
        Wend
           
    Next iFolder
   
'   --------------------------------------------------
'           Update file Setup Sheet Template
'   --------------------------------------------------

'    On Error Resume Next
'    Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & sTemplateFileName)
'    On Error GoTo 0

'    If Not wbTarget Is Nothing _
'     Then
                   
'        With wbTarget
'            On Error Resume Next
'            Set wsTargetTooling = .Worksheets("Tooling")
'            On Error GoTo 0
           
'           Do the update of the workbook, call sub that does it.
'            If Not wsTargetTooling Is Nothing _
'             Then
'                Call UpdateFile(wsMasterTooling, wsTargetTooling)
'            End If
       
'           Close the file, save changes
'            .Close SaveChanges:=True
           
'           increment files processed count and workbooks updated count
'            iFilesCount = iFilesCount + 1
'            iWorkbooksCount = iWorkbooksCount + 1
'        End With
       
'    End If

'   -----------------------
'           Closeout
'   -----------------------

    Application.CutCopyMode = False
   
    MsgBox "Done processing " & iFilesCount & " files." & Chr(10) _
         & iWorkbooksCount & " were Tooling workbooks", _
    vbInformation, _
    "Updating files with Toolings data"
   
End Sub

 

[mumps]

That's a lot of coding and to be honest, I don't have time to try to decipher what the code is trying to do.
To clarify:
You now have multiple sheets in the Master workbook. You want to update all the sheets in all the workbooks in a designated directory that have the same name as all the sheets in the Master so that they are the same. Is this correct? If so, what is the full path to the designated directory