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 '18 at 8:03
Pᴇʜ
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter 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 '18 at 8:03
Pᴇʜ
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 8:03
Pᴇʜ
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter 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 '18 at 8:03
Pᴇʜ
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter Young
182
edited Nov 23 '18 at 8:03
Pᴇʜ
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter Young
182
edited Nov 23 '18 at 8:03
Pᴇʜ
23.7k62952
edited Nov 23 '18 at 8:03
Pᴇʜ
23.7k62952
edited Nov 23 '18 at 8:03
Pᴇʜ
23.7k62952
23.7k62952
asked Nov 22 '18 at 17:38
Peter YoungPeter Young
182
asked Nov 22 '18 at 17:38
Peter YoungPeter Young
182
asked Nov 22 '18 at 17:38
Peter YoungPeter 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
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 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
edited Nov 22 '18 at 19:13
answered Nov 22 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
answered Nov 22 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
answered Nov 22 '18 at 18:10
Doug GlancyDoug Glancy
24.5k54485
24.5k54485
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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 18:34
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 at 18:38
I understand. Right between copying the sheet and closing the workbook.
– Doug Glancy
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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.
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 '18 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 '18 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 '18 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 '18 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 '18 at 18:20