vba to copy multiple ranges using indirect cell values

[cjcass]

On the wksheet "Criteria" I have two cells each containing formulas which return a string of text that acts as a range reference, as follows:
Cell M4 returns the following... Criteria!$J$8:$V$38
Cell M42 returns the following... Criteria!$J$47:$V$54
The two above ranges in this example are variable and will change so the vba needs to reference whatever is returned in the two cells M4 and M42.
I'm looking for some vba that will select both ranges at the same time and copy them ready for pasting as a union (as if you did it manually - ie. M42's range sits directly below M4's range).
Any help much appreciated.

 

[cjcass]

OK, I understand now. Let me see what I can do.
Is the range always being copied on the same sheet as the M4 and M42 cells?
If so, is there a reason we want the "Criteria" sheet name reference in front of the range (if everything is happening from the criteria sheet)?

Hi, thanks for your patience and help.
Yes, the ranges being copied are always on the same sheet as the cells M4 & M42, from the VBA's perspective everything is happening on the "Criteria" wksht and I will be executing the macro whilst on that sheet, so I guess we don't need it referenced in front of the range.

 

[Joe4]

Yes, that makes it much easier if we do not have that sheet reference.
Try this:

Sub MyCopyRange()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    
'   Get initial range values from cell M4 and M42
    Set rng1 = Range(Range("M4"))
    Set rng2 = Range(Range("M42"))
    
'   Combine ranges into one
    Set rng3 = Union(rng1, rng2)

'   Copy range
    rng3.Copy
           
End Sub

 

[cjcass]

Yes, that makes it much easier if we do not have that sheet reference.
Try this:

Sub MyCopyRange()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
   
'   Get initial range values from cell M4 and M42
    Set rng1 = Range(Range("M4"))
    Set rng2 = Range(Range("M42"))
   
'   Combine ranges into one
    Set rng3 = Union(rng1, rng2)

'   Copy range
    rng3.Copy
          
End Sub

That's brilliant! works perfectly and is super slick code.
Thanks again for all your help with this, much appreciated

 

[Joe4]

You are welcome!

Note we can make it even shorter by replacing are first two range variables with their references, i.e.

Sub MyCopyRange()

    Dim rng As Range
    
'   Combine ranges into one
    Set rng = Union(Range(Range("M4")), Range(Range("M42")))

'   Copy range
    rng.Copy

End Sub

Sometimes I like to break it out, as I find it is often easier to understand what is going on when you can see it piece-by-piece.
But either way works. Whatever you like better!

 

[cjcass]

You are welcome!

Note we can make it even shorter by replacing are first two range variables with their references, i.e.

Sub MyCopyRange()

    Dim rng As Range
   
'   Combine ranges into one
    Set rng = Union(Range(Range("M4")), Range(Range("M42")))

'   Copy range
    rng.Copy

End Sub

Sometimes I like to break it out, as I find it is often easier to understand what is going on when you can see it piece-by-piece.
But either way works. Whatever you like better!

I'll copy that code for future reference - it's even slicker but yes I agree it's sometimes better to see more of what is going on.
Thanks again, your code will save me huge amounts of time.