Mixing
How to copy named range from one workbook to another workbook in specific cell?
-1
Below is my original code. I am trying to change my code so that add/delete new columns/rows won't affect the new created workbook. I decided to use the name range to avoid the code crushed. (I know how to create the new range in the Name manager) Anyone knows how to adjust the code?
Dim WS As Worksheet
Dim Rng As Range
Dim myWs As Worksheet
Set myWs = ThisWorkbook.Sheets("MASTER")
Set Rng1 = myWs.Range("A1:AJ4")
Set Rng2 = myWs.Range("A85:AJ104")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng1.Copy Destination:=WS.Range("A1:AJ4")
Rng2.Copy Destination:=WS.Range("A5:AJ50")
excel vba excel-vba
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
add a comment |
-1
Below is my original code. I am trying to change my code so that add/delete new columns/rows won't affect the new created workbook. I decided to use the name range to avoid the code crushed. (I know how to create the new range in the Name manager) Anyone knows how to adjust the code?
Dim WS As Worksheet
Dim Rng As Range
Dim myWs As Worksheet
Set myWs = ThisWorkbook.Sheets("MASTER")
Set Rng1 = myWs.Range("A1:AJ4")
Set Rng2 = myWs.Range("A85:AJ104")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng1.Copy Destination:=WS.Range("A1:AJ4")
Rng2.Copy Destination:=WS.Range("A5:AJ50")
excel vba excel-vba
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05
add a comment |
-1
-1
-1
Below is my original code. I am trying to change my code so that add/delete new columns/rows won't affect the new created workbook. I decided to use the name range to avoid the code crushed. (I know how to create the new range in the Name manager) Anyone knows how to adjust the code?
Dim WS As Worksheet
Dim Rng As Range
Dim myWs As Worksheet
Set myWs = ThisWorkbook.Sheets("MASTER")
Set Rng1 = myWs.Range("A1:AJ4")
Set Rng2 = myWs.Range("A85:AJ104")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng1.Copy Destination:=WS.Range("A1:AJ4")
Rng2.Copy Destination:=WS.Range("A5:AJ50")
excel vba excel-vba
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
Below is my original code. I am trying to change my code so that add/delete new columns/rows won't affect the new created workbook. I decided to use the name range to avoid the code crushed. (I know how to create the new range in the Name manager) Anyone knows how to adjust the code?
Dim WS As Worksheet
Dim Rng As Range
Dim myWs As Worksheet
Set myWs = ThisWorkbook.Sheets("MASTER")
Set Rng1 = myWs.Range("A1:AJ4")
Set Rng2 = myWs.Range("A85:AJ104")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng1.Copy Destination:=WS.Range("A1:AJ4")
Rng2.Copy Destination:=WS.Range("A5:AJ50")
excel vba excel-vba
excel vba excel-vba
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
edited Jan 7 at 8:48
Pᴇʜ
24.7k63052
24.7k63052
asked Jan 2 at 16:25
JenniferzJenniferz
33
asked Jan 2 at 16:25
JenniferzJenniferz
33
asked Jan 2 at 16:25
JenniferzJenniferz
33
33
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05
add a comment |
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05
add a comment |
1 Answer
1
active
oldest
votes
0
You need to dim each workbook and worksheet you will be using.
Dim wb1 as Workbook, wb2 as Workbook, ws1 as Worksheet, ws2 as Worksheet
Dim xrow as long, arrData() as variant
Set wb1 = Workbooks("Book1")
Set ws1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks("Book2")
Set ws2 = wb2.Worksheets("Sheet2")
Note that this only works if you have both workbooks open.
Also I would recommend copying the data from each cell to an array, instead of copying a range. I get less errors this way.
ApplicationScreenUpdating = False
ws1.Activate
'where n is the number of rows you want to copy
for x = 1 To n
arrData[x - 1] = ws1.Cells(x, 1).value
next x
ws2.Activate
for i = 1 to n
ws2.Cells(i, 1).value = arrData[i - 1]
next i
ApplicationScreenUpdating = True
answered Jan 2 at 16:50
JaneJane
1,159518
add a comment |
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%2f54009792%2fhow-to-copy-named-range-from-one-workbook-to-another-workbook-in-specific-cell%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
0
You need to dim each workbook and worksheet you will be using.
Dim wb1 as Workbook, wb2 as Workbook, ws1 as Worksheet, ws2 as Worksheet
Dim xrow as long, arrData() as variant
Set wb1 = Workbooks("Book1")
Set ws1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks("Book2")
Set ws2 = wb2.Worksheets("Sheet2")
Note that this only works if you have both workbooks open.
Also I would recommend copying the data from each cell to an array, instead of copying a range. I get less errors this way.
ApplicationScreenUpdating = False
ws1.Activate
'where n is the number of rows you want to copy
for x = 1 To n
arrData[x - 1] = ws1.Cells(x, 1).value
next x
ws2.Activate
for i = 1 to n
ws2.Cells(i, 1).value = arrData[i - 1]
next i
ApplicationScreenUpdating = True
answered Jan 2 at 16:50
JaneJane
1,159518
add a comment |
0
You need to dim each workbook and worksheet you will be using.
Dim wb1 as Workbook, wb2 as Workbook, ws1 as Worksheet, ws2 as Worksheet
Dim xrow as long, arrData() as variant
Set wb1 = Workbooks("Book1")
Set ws1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks("Book2")
Set ws2 = wb2.Worksheets("Sheet2")
Note that this only works if you have both workbooks open.
Also I would recommend copying the data from each cell to an array, instead of copying a range. I get less errors this way.
ApplicationScreenUpdating = False
ws1.Activate
'where n is the number of rows you want to copy
for x = 1 To n
arrData[x - 1] = ws1.Cells(x, 1).value
next x
ws2.Activate
for i = 1 to n
ws2.Cells(i, 1).value = arrData[i - 1]
next i
ApplicationScreenUpdating = True
answered Jan 2 at 16:50
JaneJane
1,159518
add a comment |
0
0
0
You need to dim each workbook and worksheet you will be using.
Dim wb1 as Workbook, wb2 as Workbook, ws1 as Worksheet, ws2 as Worksheet
Dim xrow as long, arrData() as variant
Set wb1 = Workbooks("Book1")
Set ws1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks("Book2")
Set ws2 = wb2.Worksheets("Sheet2")
Note that this only works if you have both workbooks open.
Also I would recommend copying the data from each cell to an array, instead of copying a range. I get less errors this way.
ApplicationScreenUpdating = False
ws1.Activate
'where n is the number of rows you want to copy
for x = 1 To n
arrData[x - 1] = ws1.Cells(x, 1).value
next x
ws2.Activate
for i = 1 to n
ws2.Cells(i, 1).value = arrData[i - 1]
next i
ApplicationScreenUpdating = True
answered Jan 2 at 16:50
JaneJane
1,159518
You need to dim each workbook and worksheet you will be using.
Dim wb1 as Workbook, wb2 as Workbook, ws1 as Worksheet, ws2 as Worksheet
Dim xrow as long, arrData() as variant
Set wb1 = Workbooks("Book1")
Set ws1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks("Book2")
Set ws2 = wb2.Worksheets("Sheet2")
Note that this only works if you have both workbooks open.
Also I would recommend copying the data from each cell to an array, instead of copying a range. I get less errors this way.
ApplicationScreenUpdating = False
ws1.Activate
'where n is the number of rows you want to copy
for x = 1 To n
arrData[x - 1] = ws1.Cells(x, 1).value
next x
ws2.Activate
for i = 1 to n
ws2.Cells(i, 1).value = arrData[i - 1]
next i
ApplicationScreenUpdating = True
answered Jan 2 at 16:50
JaneJane
1,159518
answered Jan 2 at 16:50
JaneJane
1,159518
answered Jan 2 at 16:50
JaneJane
1,159518
answered Jan 2 at 16:50
JaneJane
1,159518
1,159518
add a comment |
add a comment |
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%2f54009792%2fhow-to-copy-named-range-from-one-workbook-to-another-workbook-in-specific-cell%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
I don't see any columns being added or deleted or any Named Ranges being used. Can you please edit your question to clarify what the problem is?
– cybernetic.nomad
Jan 2 at 16:32
I am trying to change my code so that add/delete new columns/rows won't affect my new created workbook.
– Jenniferz
Jan 2 at 16:37
They shouldn't, if you assign the ranges correctly...right? I'm not sure what the question is, can you please clarify?
– BruceWayne
Jan 2 at 16:51
okay, if I insert the value into A1 in the original workbook, then the value in new workbook will move to right, how can I lock it?
– Jenniferz
Jan 2 at 17:05