[Excel VBA] Replace sheet with another
grotone
Posted messages
14
Registration date
Status
Member
-
plop -
plop -
Hello,
I have 2 different Excel files, and I’m looking to replace a sheet from one workbook with a sheet from the second workbook.
Workbooks(1).Sheets(ActiveSheet.Name).Delete
Workbooks(2).ActiveSheet.Copy after:=Workbooks(1).Sheets("Navigation")
Basically, I’m deleting the sheet from workbook 1 (let's call it Sheet1), and then I'm fetching the same sheet (with the same name, Sheet1 and the same format) from workbook 2 and copying it into workbook 1.
Everything would go well in the best of worlds, but here’s the issue: in workbook 1, I have another sheet (let's say "Summary") that depends on values from the sheet I'm trying to replace, and when I perform my replacement, the links on that sheet are "broken," even though the two sheets (Sheet1 from both workbooks) are identical.
How can I replace Sheet1 while ensuring that the links in Summary do not get "broken"?
Thank you in advance
Grotone
I have 2 different Excel files, and I’m looking to replace a sheet from one workbook with a sheet from the second workbook.
Workbooks(1).Sheets(ActiveSheet.Name).Delete
Workbooks(2).ActiveSheet.Copy after:=Workbooks(1).Sheets("Navigation")
Basically, I’m deleting the sheet from workbook 1 (let's call it Sheet1), and then I'm fetching the same sheet (with the same name, Sheet1 and the same format) from workbook 2 and copying it into workbook 1.
Everything would go well in the best of worlds, but here’s the issue: in workbook 1, I have another sheet (let's say "Summary") that depends on values from the sheet I'm trying to replace, and when I perform my replacement, the links on that sheet are "broken," even though the two sheets (Sheet1 from both workbooks) are identical.
How can I replace Sheet1 while ensuring that the links in Summary do not get "broken"?
Thank you in advance
Grotone
Configuration: Windows 2003 Internet Explorer 6.0
5 answers
Hello everyone,
If you're reading the other members' solutions and you don't understand anything at all or everything seems too complex, here is the most effective and simplest solution for you:
Let's take the following case as an example:
You want to replace Sheet2 of WorkbookA with Sheet2 of WorkbookB.
However, you don't want to lose the formulas in Sheet1 that refer to Sheet2 of WorkbookA. (this paragraph may be difficult to understand at first, but make sure you grasp what is written before continuing)
Here's what you need to do:
1. Open both workbooks
2. In WorkbookB, click on Sheet2
3. In the Home menu, click on Format > Move or Copy Sheet...
4. A window appears! In this window, under the To book menu, choose WorkbookA. Still in this window, check Create a copy (at the bottom left), then click on OK
5. At this point, you should be automatically redirected to WorkbookA. You will notice that you now have Sheet2 and Sheet2 (2). You need to delete Sheet2
6. Rename Sheet2 (2) to Sheet2
7. Then, go to Sheet1: you will notice that the functions referring to Sheet2 no longer work and are replaced by #REF. This is normal. Now, select all the cells and use the Replace function (CTRL+H)
8. A window appears. In this window, in the first section, write #REF. In the second section, write Sheet2. Finally, click on Replace All (at the bottom left) and you're done!
Please note that this solution works for Excel 2010, but I'm not sure if it applies to other versions of Excel. Also, before starting the steps, make sure that no cell in Sheet1 contains an error, that is to say #REF. Otherwise, step 8 might replace #REF with Sheet2 in your formulas.
That's it! I hope my explanations have been helpful to you!
Have a great day everyone!
--
Mathieu
If you're reading the other members' solutions and you don't understand anything at all or everything seems too complex, here is the most effective and simplest solution for you:
Let's take the following case as an example:
You want to replace Sheet2 of WorkbookA with Sheet2 of WorkbookB.
However, you don't want to lose the formulas in Sheet1 that refer to Sheet2 of WorkbookA. (this paragraph may be difficult to understand at first, but make sure you grasp what is written before continuing)
Here's what you need to do:
1. Open both workbooks
2. In WorkbookB, click on Sheet2
3. In the Home menu, click on Format > Move or Copy Sheet...
4. A window appears! In this window, under the To book menu, choose WorkbookA. Still in this window, check Create a copy (at the bottom left), then click on OK
5. At this point, you should be automatically redirected to WorkbookA. You will notice that you now have Sheet2 and Sheet2 (2). You need to delete Sheet2
6. Rename Sheet2 (2) to Sheet2
7. Then, go to Sheet1: you will notice that the functions referring to Sheet2 no longer work and are replaced by #REF. This is normal. Now, select all the cells and use the Replace function (CTRL+H)
8. A window appears. In this window, in the first section, write #REF. In the second section, write Sheet2. Finally, click on Replace All (at the bottom left) and you're done!
Please note that this solution works for Excel 2010, but I'm not sure if it applies to other versions of Excel. Also, before starting the steps, make sure that no cell in Sheet1 contains an error, that is to say #REF. Otherwise, step 8 might replace #REF with Sheet2 in your formulas.
That's it! I hope my explanations have been helpful to you!
Have a great day everyone!
--
Mathieu
It is not possible (to my knowledge) for your links to remain unbroken when you delete your first sheet. Here are 2 solutions for you:
- You recreate your links with your macro (overwriting your formulas)
- You do a copy-paste between your two sheets (maybe with a complete deletion of the destination sheet before pasting)
I don't know if this will suit you, but I can't see any other solutions.
- You recreate your links with your macro (overwriting your formulas)
- You do a copy-paste between your two sheets (maybe with a complete deletion of the destination sheet before pasting)
I don't know if this will suit you, but I can't see any other solutions.
Thank you, I finally managed to get by by mixing the two.
For those interested, basically, I have a first workbook with a sheet "Feuil1" and a sheet "Résumé" that depends on values from Feuil1, but also on other sheets in this workbook that won't change. I have a second workbook with the same 2 sheets as the first workbook (but with changing data).
I'm looking to replace "Feuil1" from the first workbook with "Feuil1" from the second, while ensuring that the values that "Résumé" from the first workbook depends on do not have broken links (links pointing to the new "Feuil1" but also to the other sheets that do not change).
After a headache, here's what I do,
I delete "Résumé" from workbook 2, and replace it with "Résumé" from workbook 1, and I correct the links of workbook 2 with ChangeLinks.
Then, I delete "Feuil1" and "Résumé" from workbook 1, and replace them with those from workbook 2, and I correct the links of workbook 1 again with ChangeLinks.
All this with Application.DisplayAlerts = False so that Excel doesn't bother me in the middle of the operations.
It works and does exactly what I want, but it's quite messy as a method, if anyone has a better idea, I'm definitely open to suggestions.
Sub Importer()
Dim classeur As String
Dim feuille As String
Dim chemin As String
classeur = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose an Excel file")
Workbooks.Open classeur
feuille = Workbooks(2).ActiveSheet.Name
'step1
Application.DisplayAlerts = False
Workbooks(2).Sheets("Résumé").Delete
Workbooks(1).Sheets("Résumé").Copy after:=Workbooks(2).Sheets("Navigation")
alinks = Workbooks(2).LinkSources(xlExcelLinks)
chemin = Workbooks(2).Path & "\" & feuille & ".xls"
Workbooks(2).ChangeLink alinks(1), chemin, xlExcelLinks
'step2
Application.DisplayAlerts = False
Workbooks(1).Sheets(feuille).Delete
Workbooks(1).Sheets("Résumé").Delete
Workbooks(2).Sheets(feuille).Copy after:=Workbooks(1).Sheets("Navigation")
Workbooks(2).Sheets("Résumé").Copy after:=Workbooks(1).Sheets("Navigation")
alinks = Workbooks(1).LinkSources(xlExcelLinks)
chemin = Workbooks(1).Path & "\" & Workbooks(1).Name
Workbooks(1).ChangeLink alinks(1), chemin, xlExcelLinks
Application.DisplayAlerts = True
End Sub
a+
Grotone
For those interested, basically, I have a first workbook with a sheet "Feuil1" and a sheet "Résumé" that depends on values from Feuil1, but also on other sheets in this workbook that won't change. I have a second workbook with the same 2 sheets as the first workbook (but with changing data).
I'm looking to replace "Feuil1" from the first workbook with "Feuil1" from the second, while ensuring that the values that "Résumé" from the first workbook depends on do not have broken links (links pointing to the new "Feuil1" but also to the other sheets that do not change).
After a headache, here's what I do,
I delete "Résumé" from workbook 2, and replace it with "Résumé" from workbook 1, and I correct the links of workbook 2 with ChangeLinks.
Then, I delete "Feuil1" and "Résumé" from workbook 1, and replace them with those from workbook 2, and I correct the links of workbook 1 again with ChangeLinks.
All this with Application.DisplayAlerts = False so that Excel doesn't bother me in the middle of the operations.
It works and does exactly what I want, but it's quite messy as a method, if anyone has a better idea, I'm definitely open to suggestions.
Sub Importer()
Dim classeur As String
Dim feuille As String
Dim chemin As String
classeur = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Choose an Excel file")
Workbooks.Open classeur
feuille = Workbooks(2).ActiveSheet.Name
'step1
Application.DisplayAlerts = False
Workbooks(2).Sheets("Résumé").Delete
Workbooks(1).Sheets("Résumé").Copy after:=Workbooks(2).Sheets("Navigation")
alinks = Workbooks(2).LinkSources(xlExcelLinks)
chemin = Workbooks(2).Path & "\" & feuille & ".xls"
Workbooks(2).ChangeLink alinks(1), chemin, xlExcelLinks
'step2
Application.DisplayAlerts = False
Workbooks(1).Sheets(feuille).Delete
Workbooks(1).Sheets("Résumé").Delete
Workbooks(2).Sheets(feuille).Copy after:=Workbooks(1).Sheets("Navigation")
Workbooks(2).Sheets("Résumé").Copy after:=Workbooks(1).Sheets("Navigation")
alinks = Workbooks(1).LinkSources(xlExcelLinks)
chemin = Workbooks(1).Path & "\" & Workbooks(1).Name
Workbooks(1).ChangeLink alinks(1), chemin, xlExcelLinks
Application.DisplayAlerts = True
End Sub
a+
Grotone
Yeah ...
finally it works for cell links, but it messes up when I use the linkedcell property of an OptionButton.
the LinkedCell becomes #REF'!C200 for example, and ChangeLink can't do anything about it...
finally it works for cell links, but it messes up when I use the linkedcell property of an OptionButton.
the LinkedCell becomes #REF'!C200 for example, and ChangeLink can't do anything about it...
Hello,
and using a 3rd intermediate workbook with the names of the related sheets?
It would look something like this:
'link preservation
eric
You will never respond to an unsolicited PM...
Well, that's done.
and using a 3rd intermediate workbook with the names of the related sheets?
It would look something like this:
'link preservation
ActiveWorkbook.ChangeLink Name:="D:\Users\Eric\Documents\ccm\Workbook2.xls", _ NewName:="D:\Users\Eric\Documents\ccm\Workbook3.xls", Type:=xlExcelLinks ' replacing the sheet '.... ' restoring the links ActiveWorkbook.ChangeLink Name:="D:\Users\Eric\Documents\ccm\Workbook3.xls", _ NewName:="D:\Users\Eric\Documents\ccm\Workbook2.xls", Type:=xlExcelLinks
eric
You will never respond to an unsolicited PM...
Well, that's done.
did you notice that the question is from 2008, and that he wanted to do it in VBA?
eric
Have a good day!
Mathieu
I used the "record a macro" option while doing the steps you mentioned above. And it works great!
I just have to slightly modify the created macro to duplicate it for other files and voilà, I've refreshed my entire document!
Quick and easy.
Thanks for writing this off-topic but very useful post xD