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

Dinhosa

New Member
Joined
Aug 10, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top