Copy and replace "Master" sheet in several workbooks

  • Hi All,

    I would really appreciate your help.

    I have Master Excel file with "Master" Sheet. In one folder I have 20 excel files and all of them have sheet "Master".
    Now I want when I made modification in "Master" Sheet in Master file to automatically copy and replace in all Excel files.

    I was using a code:

    Sub LoopThroughFolder()
    Dim MyFile As String, Str As String, MyDir As String, wb As Workbook
    Dim Rws As Long, Rng As Range
    Dim CopySht As Worksheet
    Set wb = ThisWorkbook
    Set CopySht = wb.Sheets("Master")
    'change the address to suite
    MyDir = "C:\users$\Desktop\Neuer Ordner (2)" 'Your folder location, don't forget the ""
    MyFile = Dir(MyDir & "*.xlsm") 'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0
    Do While MyFile <> ""
    If MyFile <> wb.Name Then
    Workbooks.Open (MyFile)

    With ActiveWorkbook

    CopySht.Copy Before:=.Sheets(1)

    .Close True
    End With
    End If
    MyFile = Dir()
    End Sub

    But every time when I start macro screen freeze.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!