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,

Before

After, not sure why everything in to_range is gone

After

Desired, just want to overwrite to_range with from_range

Desired

Could someone explain what's going on here? Thanks.

Garry W
  • 303
  • 2
  • 10
  • 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 '18 at 18:09
  • Are you getting an error, or it just empties the `to_range`? Verify the named ranges, are they the same size? – Mathieu Guindon Oct 22 '18 at 18:10
  • @MathieuGuindon The OP is showing what happens: it clears out the `to_range` (I've reproduced that). – GSerg Oct 22 '18 at 18:11
  • 1
    @GSerg fair enough, though "it's not working" isn't the most descriptive, esp. when images don't render. – Mathieu Guindon Oct 22 '18 at 18:12
  • What are `Range("from_range").Address` and `Range("to_range").Address` values? – Mathieu Guindon Oct 22 '18 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](https://stackoverflow.com/q/32996772/11683) has something to do with it. – GSerg Oct 22 '18 at 18:20
  • @GSerg Thank you! Adding .value worked. I'm still curious as to why the code does what it does, I will read into the discussion you shared. – Garry W Oct 22 '18 at 18:25
  • 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 '18 at 18:29
  • Morale of the story: write code that *says what it does*, and *does what it says* - i.e. avoid implicit default member calls, invoke `.Value` explicitly. – Mathieu Guindon Oct 22 '18 at 19:32
  • 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 '18 at 01:57

2 Answers2

2

also you can do this job with Select:

Range("from_range").Select
Selection.Copy
Range("to_range").Select
ActiveSheet.Paste
ali
  • 23
  • 7
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.

Pedro Azzam
  • 26
  • 3
  • 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 '18 at 14:08