using predefined names in VBA
6
The code is really simple and I'm just trying to copy values in from_range and paste them in to_range. But it's just not working out...
Sub test14()
Range("to_range") = Range("from_range")
End Sub
Before,
After, not sure why everything in to_range is gone
Desired, just want to overwrite to_range with from_range
Could someone explain what's going on here? Thanks.
vba
asked Oct 22 at 17:56
Garry W
767
|
show 5 more comments
6
The code is really simple and I'm just trying to copy values in from_range and paste them in to_range. But it's just not working out...
Sub test14()
Range("to_range") = Range("from_range")
End Sub
Before,
After, not sure why everything in to_range is gone
Desired, just want to overwrite to_range with from_range
Could someone explain what's going on here? Thanks.
vba
asked Oct 22 at 17:56
Garry W
767
3
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of.ValueafterRange("from_range");Range("to_range")may or may not have.Value, that does not change the outcome.
– GSerg
Oct 22 at 18:09
1
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
2
It's extra weird because explicitly calling the default property (Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.
– GSerg
Oct 22 at 18:20
1
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
1
Oddly enoughRange("to")=[{1,2,3,4}]works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to beSETafter all. Strange how using properties fixes the problem.
– Michal Rosa
Nov 17 at 1:57
|
show 5 more comments
6
6
6
3
The code is really simple and I'm just trying to copy values in from_range and paste them in to_range. But it's just not working out...
Sub test14()
Range("to_range") = Range("from_range")
End Sub
Before,
After, not sure why everything in to_range is gone
Desired, just want to overwrite to_range with from_range
Could someone explain what's going on here? Thanks.
vba
asked Oct 22 at 17:56
Garry W
767
The code is really simple and I'm just trying to copy values in from_range and paste them in to_range. But it's just not working out...
Sub test14()
Range("to_range") = Range("from_range")
End Sub
Before,
After, not sure why everything in to_range is gone
Desired, just want to overwrite to_range with from_range
Could someone explain what's going on here? Thanks.
vba
vba
asked Oct 22 at 17:56
Garry W
767
asked Oct 22 at 17:56
Garry W
767
edited Nov 20 at 18:25
asked Oct 22 at 17:56
Garry W
767
asked Oct 22 at 17:56
Garry W
767
asked Oct 22 at 17:56
Garry W
767
767
3
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of.ValueafterRange("from_range");Range("to_range")may or may not have.Value, that does not change the outcome.
– GSerg
Oct 22 at 18:09
1
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
2
It's extra weird because explicitly calling the default property (Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.
– GSerg
Oct 22 at 18:20
1
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
1
Oddly enoughRange("to")=[{1,2,3,4}]works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to beSETafter all. Strange how using properties fixes the problem.
– Michal Rosa
Nov 17 at 1:57
|
show 5 more comments
3
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of.ValueafterRange("from_range");Range("to_range")may or may not have.Value, that does not change the outcome.
– GSerg
Oct 22 at 18:09
1
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
2
It's extra weird because explicitly calling the default property (Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.
– GSerg
Oct 22 at 18:20
1
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
1
Oddly enoughRange("to")=[{1,2,3,4}]works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to beSETafter all. Strange how using properties fixes the problem.
– Michal Rosa
Nov 17 at 1:57
3
3
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of .Value after Range("from_range"); Range("to_range") may or may not have .Value, that does not change the outcome.– GSerg
Oct 22 at 18:09
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of .Value after Range("from_range"); Range("to_range") may or may not have .Value, that does not change the outcome.– GSerg
Oct 22 at 18:09
1
1
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
2
2
It's extra weird because explicitly calling the default property (
Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.– GSerg
Oct 22 at 18:20
It's extra weird because explicitly calling the default property (
Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.– GSerg
Oct 22 at 18:20
1
1
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
1
1
Oddly enough
Range("to")=[{1,2,3,4}] works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to be SET after all. Strange how using properties fixes the problem.– Michal Rosa
Nov 17 at 1:57
Oddly enough
Range("to")=[{1,2,3,4}] works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to be SET after all. Strange how using properties fixes the problem.– Michal Rosa
Nov 17 at 1:57
|
show 5 more comments
2 Answers
2
active
oldest
votes
1
I solved the question coping the range and pasting special like explained above:
Sub test14()
Range("from_range").Copy
Range("to_range").PasteSpecial xlPasteValues
End Sub
I hope it helps you!
Regards,
Pedro Azzam.
answered Nov 21 at 13:03
Pedro Azzam
263
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
add a comment |
2
also you can do this job with Select:
Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
answered Nov 21 at 13:11
ali
387
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%2f52935105%2fusing-predefined-names-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I solved the question coping the range and pasting special like explained above:
Sub test14()
Range("from_range").Copy
Range("to_range").PasteSpecial xlPasteValues
End Sub
I hope it helps you!
Regards,
Pedro Azzam.
answered Nov 21 at 13:03
Pedro Azzam
263
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
add a comment |
1
I solved the question coping the range and pasting special like explained above:
Sub test14()
Range("from_range").Copy
Range("to_range").PasteSpecial xlPasteValues
End Sub
I hope it helps you!
Regards,
Pedro Azzam.
answered Nov 21 at 13:03
Pedro Azzam
263
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
add a comment |
1
1
1
I solved the question coping the range and pasting special like explained above:
Sub test14()
Range("from_range").Copy
Range("to_range").PasteSpecial xlPasteValues
End Sub
I hope it helps you!
Regards,
Pedro Azzam.
answered Nov 21 at 13:03
Pedro Azzam
263
I solved the question coping the range and pasting special like explained above:
Sub test14()
Range("from_range").Copy
Range("to_range").PasteSpecial xlPasteValues
End Sub
I hope it helps you!
Regards,
Pedro Azzam.
answered Nov 21 at 13:03
Pedro Azzam
263
answered Nov 21 at 13:03
Pedro Azzam
263
answered Nov 21 at 13:03
Pedro Azzam
263
answered Nov 21 at 13:03
Pedro Azzam
263
263
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
add a comment |
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
With this code, you can paste only values from a range to other, so all the formated cells keep like before. If you want to paste formats as well, just change "PasteSpecial xlPasteValues" for "Paste". Regards, Pedro Azzam.
– Pedro Azzam
Nov 21 at 14:08
add a comment |
2
also you can do this job with Select:
Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
answered Nov 21 at 13:11
ali
387
add a comment |
2
also you can do this job with Select:
Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
answered Nov 21 at 13:11
ali
387
add a comment |
2
2
2
also you can do this job with Select:
Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
answered Nov 21 at 13:11
ali
387
also you can do this job with Select:
Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
answered Nov 21 at 13:11
ali
387
answered Nov 21 at 13:11
ali
387
answered Nov 21 at 13:11
ali
387
answered Nov 21 at 13:11
ali
387
387
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.
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%2f52935105%2fusing-predefined-names-in-vba%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
3
Range("to_range").Value = Range("from_range").Value, but I wonder why that does not happen automatically, because it should. Specifically it's the absence of.ValueafterRange("from_range");Range("to_range")may or may not have.Value, that does not change the outcome.– GSerg
Oct 22 at 18:09
1
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render.
– Mathieu Guindon
Oct 22 at 18:12
2
It's extra weird because explicitly calling the default property (
Range("to_range").value = Range("from_range").[_Default]) does the right thing, but implicitly it does not. I wonder if this discussion has something to do with it.– GSerg
Oct 22 at 18:20
1
@MathieuGuindon I'm not getting any error. The code just empties the to_range, at least that's what it appears to be. The colored ranges in the pictures are to_range and from_range. Sorry I just thought posting images are the most efficient way to describe it. You can try this code with any named ranges with the same dimensions.
– Garry W
Oct 22 at 18:29
1
Oddly enough
Range("to")=[{1,2,3,4}]works just fine. It must have something to do with how Excel handles ranges which are objects and not variables - they have to beSETafter all. Strange how using properties fixes the problem.– Michal Rosa
Nov 17 at 1:57