Jan 31 2024 02:56 AM
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
Jan 31 2024 03:39 AM
SolutionYou 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:
2. Open the workbook containing the sheet you want to copy:
3. Press ALT + F11 to open the VBA editor:
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
5. Run the VBA code:
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.
Jan 31 2024 07:17 AM
Jan 31 2024 11:11 AM
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.
Feb 01 2024 08:14 AM
This is great - thanks you so much Nikolino for your time. You have saved me a tonne of work!
Feb 08 2024 05:18 AM
@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
Feb 08 2024 05:38 AM
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:
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.
Feb 08 2024 06:35 AM
Feb 08 2024 10:08 PM
Feb 09 2024 03:13 AM
@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.
Mar 27 2024 07:11 AM
Jan 31 2024 03:39 AM
SolutionYou 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:
2. Open the workbook containing the sheet you want to copy:
3. Press ALT + F11 to open the VBA editor:
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
5. Run the VBA code:
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.