VBA Loop to replace a worksheet in multiple workbooks


New Member
Sep 5, 2019

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
  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"
            MsgBox "You selected sheet " & uiSheet.Name
    Set sheetupdate = uiSheet
    End If


'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
    '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

    'Get next file name
      myFile = Dir

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

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

End Sub

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Latest member