VBA Loop to replace a worksheet in multiple workbooks

[gadisal]

Hello,

It is the first time I use VBA and I am struggling aquite q bit with a task.

Here is the issue: I have some 120 workbooks in a folder, and all have the same structure (same number of sheets, same names, and same order). If a user changes a sheet in one of these workbooks (by adding for example a formula), this sheet should replace the sheet with the same name in all the other 120 workbooks.

So far, I have managed to write a code prompting the user to select a sheet, and found some code to select a folder where all the other files are located (thanks to the spreadsheet guru) I am really stuck at the point where I need to move the sheet from the source workbook to the destination workbooks.

I am really a newbie and would appreciate any help.

Here is what I have already done:

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim fullpath As String
Dim sheetupdate As Worksheet


Dim myExtension As String
Dim FldrPicker As FileDialog
Dim uiSheet As Worksheet
Dim fd As Office.FileDialog




'Optimize Macro Speed
  'Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
    With FldrPicker
      .Title = "Select Folder where documents to alter are located"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings
      
'Select file


  Set fd = Application.FileDialog(msoFileDialogFilePicker)


    With fd
      .Title = "Select Master File"
      .Filters.Add "Excel files", "*.xls*", 1
      .AllowMultiSelect = False
      
        If .Show = True Then
            fullpath = .SelectedItems.Item(1)
        End If
    End With
    
    On Error Resume Next
    Set wb = Workbooks("fullpath")


    Workbooks.Open fullpath
    
        Set uiSheet = Application.InputBox("Click on a cell", Type:=8).Parent
        On Error GoTo 0
            If uiSheet Is Nothing Then
            MsgBox "canceled"
            Else
            MsgBox "You selected sheet " & uiSheet.Name
    
    Set sheetupdate = uiSheet
    
  
            
    End If


wb.Sheets(uiSheet.Name).Copy


        
'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
          
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Change First Worksheet's Background Fill Blue
      
       wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents


    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Task Complete!"


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub