How do I copy a sheet to multiple other workbooks?

[Anna_Annikins]

Hi,

I have a sheet in one workbook full of formulae that took me ages to do. Formulae draw links from other sheets in the same workbook. I want to copy this sheet to multiple other workbooks (about 100 of them) so that the same calculations can be done on data from the other workbooks too. Is there a faster way of doing this than individually copying over the sheet every time?

Thanks,

Anna

[#2 · Accepted solution]

@Anna_Annikins 

You can use a combination of VBA (Visual Basic for Applications) to automate the process of copying a sheet to multiple workbooks. Here's a step-by-step guide:

1. Backup your workbooks:

• Before running any VBA code, it's always a good practice to backup your workbooks to prevent accidental data loss.

2. Open the workbook containing the sheet you want to copy:

• This will be your source workbook.

3. Press ALT + F11 to open the VBA editor:

• In the VBA editor, right-click on "VBAProject (YourWorkbookName)" in the left pane.
• Choose "Insert" -> "Module" to insert a new module.

4. Copy and paste the following VBA code into the module:

Vba code in untested, please backup before use it.

Sub CopySheetToOtherWorkbooks()
    Dim SourceSheet As Worksheet
    Dim TargetWorkbook As Workbook
    Dim TargetPath As String
    Dim TargetWorkbookName As String
    
    ' Set the source sheet
    Set SourceSheet = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Specify the target path where other workbooks are located
    TargetPath = "C:\Path\To\Your\Workbooks\" ' Replace with the actual path
    
    ' Loop through all workbooks in the target path
    TargetWorkbookName = Dir(TargetPath & "*.xlsx")
    Do While TargetWorkbookName <> ""
        ' Open the target workbook
        Set TargetWorkbook = Workbooks.Open(TargetPath & TargetWorkbookName)
        
        ' Copy the source sheet to the target workbook
        SourceSheet.Copy Before:=TargetWorkbook.Sheets(1)
        
        ' Close and save changes to the target workbook
        TargetWorkbook.Close SaveChanges:=True
        
        ' Move to the next workbook
        TargetWorkbookName = Dir
    Loop
End Sub

• Replace "YourSheetName" with the name of the sheet you want to copy.
• Replace "C:\Path\To\Your\Workbooks" with the actual path where your other workbooks are located.

5. Run the VBA code:

• Close the VBA editor and press ALT + F8 to open the "Macro" dialog.
• Select "CopySheetToOtherWorkbooks" and click "Run."

This VBA code will loop through all workbooks in the specified path and copy the specified sheet from the source workbook to each of them. Make sure that the sheet name matches exactly, and the target path is correct.

Note: VBA needs to be enabled in your Excel settings for this to work. Additionally, depending on your security settings, you might need to adjust macro settings to allow running VBA code.  The text, steps and the code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

This looks amazing - thank you so much! Could I also ask - how to I set each new workbook as source data? When sheets copy, they automaticlaly set the origianl workbook as the source (for formulae that take values from other sheets in the workbook). I don't want it to do this. Each workbook contains new data which I want analysed. I have been doing this manually by going to Data - Edit LInks - Change source - then selecting the new workbook. Is there any way to automate this process as well? Thank you

[#4]

@Anna_Annikins 

You can use VBA to update the links in the copied sheets to refer to the respective data in each new workbook. Below is an updated version of the previous code that includes the functionality to update the source data for the formulas in the copied sheets:

Sub CopySheetAndUpdateLinks()
    Dim SourceSheet As Worksheet
    Dim TargetWorkbook As Workbook
    Dim TargetPath As String
    Dim TargetWorkbookName As String
    Dim TargetSheet As Worksheet
    
    ' Set the source sheet
    Set SourceSheet = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Specify the target path where other workbooks are located
    TargetPath = "C:\Path\To\Your\Workbooks\" ' Replace with the actual path
    
    ' Loop through all workbooks in the target path
    TargetWorkbookName = Dir(TargetPath & "*.xlsx")
    Do While TargetWorkbookName <> ""
        ' Open the target workbook
        Set TargetWorkbook = Workbooks.Open(TargetPath & TargetWorkbookName)
        
        ' Copy the source sheet to the target workbook
        SourceSheet.Copy Before:=TargetWorkbook.Sheets(1)
        
        ' Set reference to the newly copied sheet
        Set TargetSheet = TargetWorkbook.Sheets(1)
        
        ' Update links to refer to the new workbook
        UpdateLinks TargetSheet, TargetWorkbookName
        
        ' Close and save changes to the target workbook
        TargetWorkbook.Close SaveChanges:=True
        
        ' Move to the next workbook
        TargetWorkbookName = Dir
    Loop
End Sub

Sub UpdateLinks(TargetSheet As Worksheet, TargetWorkbookName As String)
    Dim Link As Variant
    Dim NewLink As String
    
    ' Loop through all external links in the target sheet
    For Each Link In TargetSheet.LinkSources(xlExcelLinks)
        ' Check if the link is related to the source workbook
        If InStr(1, Link, ThisWorkbook.Name) > 0 Then
            ' Create a new link with the target workbook name
            NewLink = Replace(Link, ThisWorkbook.Name, "[" & TargetWorkbookName & "]" & SourceSheet.Name)
            
            ' Update the link in the target sheet
            TargetSheet.ChangeLink Link, NewLink, xlLinkTypeExcelLinks
        End If
    Next Link
End Sub

This modified code includes the UpdateLinks procedure, which is called after copying the sheet to each new workbook. It loops through all external links in the copied sheet and updates the links to refer to the new workbook. The text, steps and code were created with the help of AI.

Remember to replace "YourSheetName" with the name of the sheet you want to copy, and update the TargetPath with the actual path where your other workbooks are located. Also, ensure that you've backed up your workbooks before running any VBA code.

[#5]

@NikolinoDE 

This is great - thanks you so much Nikolino for your time. You have saved me a tonne of work!

[#6]

yw

[#7]

@NikolinoDE I've just tried to run the macro and when I press run, nothing happens. There are no error messages. I've enabled macros in the security centre. Am I missing something? Screenshot below. Thanks, Anna

[#8]

@Anna_Annikins 

VBA macros typically do not work directly within files stored in cloud storage services like OneDrive. This is because VBA macros are executed within the Excel application itself, and cloud storage services often provide limited support for running executable code within their platforms due to security concerns.

However, you can still work with VBA macros in Excel files stored on OneDrive by downloading the file to your local machine, enabling macros in Excel, and then running the macros as usual. Once you've made any changes or run the macros, you can save the file back to OneDrive.

If the macro after that not running as expected. There could be a few reasons for this. Let's troubleshoot the issue:

1. Check Macro Security Settings: You mentioned that you've enabled macros in the security center, which is good. However, sometimes there are multiple levels of security settings. Ensure that macros are enabled for the specific workbook you're working with. You can check this by going to "File" > "Options" > "Trust Center" > "Trust Center Settings" > "Macro Settings" and ensuring it's set to "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)".
2. Check VBA Project References: Sometimes, the VBA code might reference external libraries or objects that are not available or properly set up in your environment. To check this, go to the VBA editor (Alt + F11), then go to "Tools" > "References" and ensure that none of the referenced libraries are marked as missing. If they are, you'll need to fix those references.
3. Check for Errors in VBA Code: Even though the code provided seems correct, there could still be syntax errors or other issues. Ensure that there are no errors highlighted in the VBA editor. If there are, you'll need to correct them before the code can run properly.
4. Try Running the Macro Step by Step: You can try running the macro step by step to see where it might be failing. To do this, set a breakpoint in the code (click on the line you want to break at and press F9), then run the macro (Alt + F8), and step through it using F8. This way, you can see which line of code is causing the issue.
5. Check Path and File Names: Ensure that the path to your target workbooks is correct and that the workbooks exist in that location. Also, make sure that the workbook names match the ones you're expecting. Even a small typo can cause the macro to fail.
6. Check for Error Messages in Immediate Window: Sometimes error messages might not appear visibly in Excel. You can check for any error messages or debug information in the Immediate Window in the VBA editor (Ctrl + G to open it).

By checking these points, you should be able to identify why the macro is not running as expected and take appropriate action to fix it.

Hope this Information helps.

[#9]

@NikolinoDE I have tried all these things and it still doesn't work :(

[#10]

Is it possible to insert the file (without sensitive data) to see where the problem might be?

[#11]

@NikolinoDE I have attached an anonymised version of the source workbook. I want to copy the sheet 'Final DataNew2' into multiple other workbooks that already contain the other sheets (to draw data from). Thanks.

[#12]

Dear @NikolinoDE,
I have applied your macro to copy the same sheet into multiple excel workbooks in the same folder and it works!
I would also need a macro to delete the same sheet from multiple workbooks and I don't know how to do it. Do you also have a macro for that?
Much appreciate your help!