Thread: Copy worksheet to all workbooks in specified folder

Hi All,

I need some help.

Another person places files in a folder (up to 100) each week. I need to copy a worksheet from a workbook into multiple workbooks in the folder previously mentioned.

I have not started writing the code because I do not know how to systematically open files in a wb one at a time and make a change.

Thanks for the help

BTW - The file names change each week with no consistency

Hello nosstech,

Before I or anyone can help you, there are some questions that need to be answered first:

1. What version of Excel are you using
2. Are these the only Excel files in the folder?
3. What is the name of the sheet to be copied?
4. Is the entire sheet to be copied or only part(s) of it?
5. Will this sheet be placed before or after a specific sheet?

Sincerely,
Leith Ross

1. What version of Excel are you using - 2003
2. Are these the only Excel files in the folder? - Yes
3. What is the name of the sheet to be copied? - Coaching Form
4. Is the entire sheet to be copied or only part(s) of it? - Entire Sheets
5. Will this sheet be placed before or after a specific sheet? - Before Sheet 1

Originally Posted by Leith Ross

Hello nosstech,

Before I or anyone can help you, there are some questions that need to be answered first:

1. What version of Excel are you using
2. Are these the only Excel files in the folder?
3. What is the name of the sheet to be copied?
4. Is the entire sheet to be copied or only part(s) of it?
5. Will this sheet be placed before or after a specific sheet?

Sincerely,
Leith Ross

Hello nosstech,

You made need to change some variables in this macro before it will run correctly. You will need to add the folder location where the workbooks are stored. Check the name of the worksheet "Sheet1". I entered it as "Sheet1" rather than "Sheet 1" as you had it. These are marked in red.

Macro Code

Sub UpdateAllWorkbooks()

  Dim MyFolder As String
  Dim SrcWks As Worksheet
  Dim Wkb As Workbook
  Dim WkbName As String
  
    Set SrcWks = ThisWorkbook.Worksheets("Coaching Form")
    MyFolder = "C:\Documents and Settings\Admin.ADMINS\My Documents"
    
      WkbName = Dir(MyFolder & "\*.xls")
        Do While WkbName <> ""
          Set Wkb = Workbooks.Open(WkbName)
            SrcWks.Copy Before:=Wkb.Worksheets("Sheet1")
            Wkb.Close SaveChanges:=True
          WkbName = Dir()
        Loop
        
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross