Change references when copying sheet to multiple workbooks
2
I am using the code below to copy a worksheet from a source workbook to several hundred destination workbooks. The source worksheet contains references (in formula) to other worksheets in the source workbook; I would like to keep these references between sheets, but in the destination workbook. Can this code be modified to do this?
Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceSheet = ActiveWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
excel vba excel-vba
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
|
show 3 more comments
2
I am using the code below to copy a worksheet from a source workbook to several hundred destination workbooks. The source worksheet contains references (in formula) to other worksheets in the source workbook; I would like to keep these references between sheets, but in the destination workbook. Can this code be modified to do this?
Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceSheet = ActiveWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
excel vba excel-vba
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
Can you add a line that does a find and replace of"["&SourceWorkbook.name&"]!"and replaces that with blank?
– Fernando J. Rivera
Nov 22 at 17:49
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
Solar Mike's suggestion is good, but you want to replace=with'=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out
– cybernetic.nomad
Nov 22 at 18:03
1
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20
|
show 3 more comments
2
2
2
1
I am using the code below to copy a worksheet from a source workbook to several hundred destination workbooks. The source worksheet contains references (in formula) to other worksheets in the source workbook; I would like to keep these references between sheets, but in the destination workbook. Can this code be modified to do this?
Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceSheet = ActiveWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
excel vba excel-vba
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
I am using the code below to copy a worksheet from a source workbook to several hundred destination workbooks. The source worksheet contains references (in formula) to other worksheets in the source workbook; I would like to keep these references between sheets, but in the destination workbook. Can this code be modified to do this?
Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceSheet = ActiveWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
edited Nov 23 at 8:03
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 22 at 17:38
Peter Young
182
asked Nov 22 at 17:38
Peter Young
182
asked Nov 22 at 17:38
Peter Young
182
182
Can you add a line that does a find and replace of"["&SourceWorkbook.name&"]!"and replaces that with blank?
– Fernando J. Rivera
Nov 22 at 17:49
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
Solar Mike's suggestion is good, but you want to replace=with'=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out
– cybernetic.nomad
Nov 22 at 18:03
1
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20
|
show 3 more comments
Can you add a line that does a find and replace of"["&SourceWorkbook.name&"]!"and replaces that with blank?
– Fernando J. Rivera
Nov 22 at 17:49
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
Solar Mike's suggestion is good, but you want to replace=with'=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out
– cybernetic.nomad
Nov 22 at 18:03
1
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20
Can you add a line that does a find and replace of
"["&SourceWorkbook.name&"]!" and replaces that with blank?– Fernando J. Rivera
Nov 22 at 17:49
Can you add a line that does a find and replace of
"["&SourceWorkbook.name&"]!" and replaces that with blank?– Fernando J. Rivera
Nov 22 at 17:49
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
Solar Mike's suggestion is good, but you want to replace
= with '=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out– cybernetic.nomad
Nov 22 at 18:03
Solar Mike's suggestion is good, but you want to replace
= with '=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out– cybernetic.nomad
Nov 22 at 18:03
1
1
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20
|
show 3 more comments
1 Answer
1
active
oldest
votes
2
Try something like this:
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim folder As String, filename As String
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceWorkbook = ActiveWorkbook
Set sourceSheet = sourceWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.ChangeLink Name:=sourceWorkbook.Name, NewName:=destinationWorkbook.Name, Type:=xlExcelLinks
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
I got this by going to Data>Edit Links with the destination workbook active and the Macro Recorder turned on, choosing "Change Source" and then browsing to the destination workbook.
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435972%2fchange-references-when-copying-sheet-to-multiple-workbooks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
Try something like this:
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim folder As String, filename As String
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceWorkbook = ActiveWorkbook
Set sourceSheet = sourceWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.ChangeLink Name:=sourceWorkbook.Name, NewName:=destinationWorkbook.Name, Type:=xlExcelLinks
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
I got this by going to Data>Edit Links with the destination workbook active and the Macro Recorder turned on, choosing "Change Source" and then browsing to the destination workbook.
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
|
show 3 more comments
2
Try something like this:
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim folder As String, filename As String
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceWorkbook = ActiveWorkbook
Set sourceSheet = sourceWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.ChangeLink Name:=sourceWorkbook.Name, NewName:=destinationWorkbook.Name, Type:=xlExcelLinks
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
I got this by going to Data>Edit Links with the destination workbook active and the Macro Recorder turned on, choosing "Change Source" and then browsing to the destination workbook.
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
|
show 3 more comments
2
2
2
Try something like this:
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim folder As String, filename As String
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceWorkbook = ActiveWorkbook
Set sourceSheet = sourceWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.ChangeLink Name:=sourceWorkbook.Name, NewName:=destinationWorkbook.Name, Type:=xlExcelLinks
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
I got this by going to Data>Edit Links with the destination workbook active and the Macro Recorder turned on, choosing "Change Source" and then browsing to the destination workbook.
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
Try something like this:
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim destinationWorkbook As Workbook
Dim folder As String, filename As String
'Worksheet in active workbook to be copied as a new sheet to the destination workbook
Set sourceWorkbook = ActiveWorkbook
Set sourceSheet = sourceWorkbook.Worksheets("Edit")
'Folder containing the destination workbooks
folder = "M:Employee InformationPeter YoungMsc Project1 - 181028 - Office First FloorMacroCopy"
filename = Dir(folder & "*.xlsx", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.ChangeLink Name:=sourceWorkbook.Name, NewName:=destinationWorkbook.Name, Type:=xlExcelLinks
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
I got this by going to Data>Edit Links with the destination workbook active and the Macro Recorder turned on, choosing "Change Source" and then browsing to the destination workbook.
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
edited Nov 22 at 19:13
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
answered Nov 22 at 18:10
Doug Glancy
24.3k54484
24.3k54484
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
|
show 3 more comments
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
1
1
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
Hello Doug, at which point in the code would I insert the line you suggest? I should probably have said, I have virtually no experience of this at all.
– Peter Young
Nov 22 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 at 18:38
1
1
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
When you say I will have to assign the Activeworkbook to the sourceWorkbook variable at the beginning of the procedure; is that this line "Set sourceSheet = ActiveWorkbook.Worksheets("Edit")" and what would I need to change? Thanks, Peter
– Peter Young
Nov 22 at 18:42
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
Okay, I've re-written your code to include my solution and the declarations and assignments (Dims and Sets) needed to make it work. I haven't actually run the code, so let me know if you get an error. Did you not write the original code? If you are going to keep coding, one thing is to learn to use the Macro Recorder.
– Doug Glancy
Nov 22 at 19:03
1
1
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
It works perfectly. Thank you so much for your time. This is for my Msc project which is due in 2 weeks time; this has saved me hours of time data processing. Thanks again, Peter
– Peter Young
Nov 22 at 19:36
|
show 3 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435972%2fchange-references-when-copying-sheet-to-multiple-workbooks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you add a line that does a find and replace of
"["&SourceWorkbook.name&"]!"and replaces that with blank?– Fernando J. Rivera
Nov 22 at 17:49
why not remove the "=" from all the cells, then move the sheet and replace the "=" when the sheet is in the destination?
– Solar Mike
Nov 22 at 17:54
@SolarMike because that way you'd need to record which cells have = and which ones dont, whereas you can easily remove the "[workbook.xlsx]!" part from every cell where it's found.
– Fernando J. Rivera
Nov 22 at 18:00
Solar Mike's suggestion is good, but you want to replace
=with'=, then remove the apostrophe after the move, thus avoiding the overhead Fernando points out– cybernetic.nomad
Nov 22 at 18:03
1
Yeah but some of his formulas can have logical tests that involve "=", those would get changed too
– Fernando J. Rivera
Nov 22 at 18:20