Copy Worksheet from master file to multiple files in a folder with VBA

[Dinhosa]

Hi, hoping someone can help me with this.

I have a workbook called Rules which contains one worksheet also called Rules. I would like to take a mirror of this worksheet in its entirety and move a copy to all the excel files in a destination folder that I specify. I am using a third file as a "Macro Control" file, it is from there I would like to write the code and run the macro. So there would be a cell in this third file which specifies where the Rules workbook is located and also the target folder that contains the excel files I want to add my worksheet to. At the time of running the macro neither the source file or the target files will be open, only the Macro Control file and once it has run through I would only want the Macro Control file still left open. The target files will never contain a worksheet called rules to begin with. The Source file, Target files and Macro Control file are all in different folders.

Hope that makes sense, I've done a fair bit of searching but had no joy, a lot of the time people are trying to do the opposite by creating a "Master File" containing data from multiple targets. The fact I'm using a third file as a control file rather than housing the macro in the source file is also causing me confusion.

Any help greatly appreciated, thanks.

 

[Trevor G]

Welcome to the forum.

The following code would require you to have a copy of the Rules worksheet within the workbook you run the code, males sure you change the path. See if this helps.

Option Explicit
Public Sub CopyRulesSheetToAllWorkbooksInFolder()

    Dim sSheet As Worksheet
    Dim folder As String, filename As String
    Dim destinationWorkbook As Workbook

    'Copy Rules worksheet in active workbook
    'to the destination woorkbooks
    Application.ScreenUpdating = False
    Set sSheet = ActiveWorkbook.Worksheets("Rules")
    
    'Folder to copy Rules into
    folder = "C:\Users\Trev\Documents\Kettle\"

    filename = Dir(folder & "*.xlsx", vbNormal)
    While Len(filename) <> 0
    'Use the immediate window to confirm workbook names
        Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        'Worksheets is copied as the first worksheet
        sSheet.Copy before:=destinationWorkbook.Sheets(1)
        destinationWorkbook.Close True
        filename = Dir()  ' Get next matching file
    Wend
    Application.ScreenUpdating = True
 End Sub