using predefined names in VBA

Question

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.

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
– GSerg, Commented Oct 22, 2018 at 18:09
@GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 22, 2018 at 18:12
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
– GSerg, Commented Oct 22, 2018 at 18:20
@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
– Garry W, Commented Oct 22, 2018 at 18:29
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
– Michal, Commented Nov 17, 2018 at 1:57

ali · Accepted Answer · 2018-11-21 13:11:21Z

2

also you can do this job with Select:

Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste

answered Nov 21, 2018 at 13:11

ali

237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pedro Azzam · Accepted Answer · 2018-11-21 13:03:14Z

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, 2018 at 13:03

Pedro Azzam

263 bronze badges

1 Comment

Pedro Azzam Over a year ago

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.

Collectives™ on Stack Overflow

using predefined names in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related