How do I copy a sheet to multiple other workbooks?

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.