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.

 

[Joe4]

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.

If it is going to be VBA code, then it really doesn't need to be done at the same time, does it? It doesn't really matter how many lines of code that you have, it will still run all together in a single VBA procedure. So why not then just have the VBA code copy and paste the first range and then copy and paste the second range underneath where it just pasted the first range?

It is two separate copy/pastes, but since it in running in VBA it will appear to be doing both simultaneously and no one is the wiser.
I don't think there is any need to make it any more complicated than that.

 

[cjcass]

If it is going to be VBA code, then it really doesn't need to be done at the same time, does it? It doesn't really matter how many lines of code that you have, it will still run all together in a single VBA procedure. So why not then just have the VBA code copy and paste the first range and then copy and paste the second range underneath where it just pasted the first range?

It is two separate copy/pastes, but since it in running in VBA it will appear to be doing both simultaneously and no one is the wiser.
I don't think there is any need to make it any more complicated than that.

Hi, thanks for your reply.
I'm not looking for the code to carry out a paste function, just copying only - the data will be pasted manually into different locations in different files.

 

[Joe4]

I am not really sure why you need/want the sheet reference if the value with the range to copy is always on the same sheet as the range you are copying.
But in any event, here is some sample code that shows you how you can split them in VBA and reference that range.
My last line is selecting the range, but you can just change ".Select" to ".Copy" to copy it. I just used "Select" as proof-of-concept to show that it works.

Sub MyTest()

    Dim myVal As String
    Dim sh As String
    Dim rng As String
    Dim arr() As String
    
'   Get value from cell M4
    myVal = Sheets("Criteria").Range("M4")
    
'   Separate sheet and range in separate variables
    arr = Split(myVal, "!")
    sh = arr(0)
    rng = arr(1)
    
'   Select range
    Sheets(sh).Range(rng).Select
           
End Sub

 

[cjcass]

I am not really sure why you need/want the sheet reference if the value with the range to copy is always on the same sheet as the range you are copying.
But in any event, here is some sample code that shows you how you can split them in VBA and reference that range.
My last line is selecting the range, but you can just change ".Select" to ".Copy" to copy it. I just used "Select" as proof-of-concept to show that it works.

Sub MyTest()

    Dim myVal As String
    Dim sh As String
    Dim rng As String
    Dim arr() As String
   
'   Get value from cell M4
    myVal = Sheets("Criteria").Range("M4")
   
'   Separate sheet and range in separate variables
    arr = Split(myVal, "!")
    sh = arr(0)
    rng = arr(1)
   
'   Select range
    Sheets(sh).Range(rng).Select
          
End Sub

Thanks for your reply and help but I'm not sure you've understood the brief (unless I'm missing something?) - your code only selects the range referenced in cell M4.

 

[Joe4]

Thanks for your reply and help but I'm not sure you've understood the brief (unless I'm missing something?) - your code only selects the range referenced in cell M4.

Yes, I know. I acknowledged that and did that just for demonstration purposes to show how you can take that value and translate it to VBA.
As I said, all you need to do to copy it is change this line:

Sheets(sh).Range(rng).Select

to this:

Sheets(sh).Range(rng).Copy

And just repeat the process for the second range.

 

[cjcass]

Yes, I know. I acknowledged that and did that just for demonstration purposes to show how you can take that value and translate it to VBA.
As I said, all you need to do to copy it is change this line:

Sheets(sh).Range(rng).Select

to this:

Sheets(sh).Range(rng).Copy

Yes, I know. I acknowledged that and did that just for demonstration purposes to show how you can take that value and translate it to VBA.
As I said, all you need to do to copy it is change this line:

Sheets(sh).Range(rng).Select

to this:

Sheets(sh).Range(rng).Copy

And just repeat the process for the second range.

I understand changing .Select to .Copy but your code isn't referencing cell M42 as well as M4 - I need the code to copy both ranges at the same time.

 

[Joe4]

I understand changing .Select to .Copy but your code isn't referencing cell M42 as well as M4 - I need the code to copy both ranges at the same time.

Please go back and read what I wrote in my first reply.

 

[cjcass]

Please go back and read what I wrote in my first reply.

Have re-read your first comment - it talks about pasting - I'm not interested in any code that pastes. I'm after some code that will copy the two ranges at the same time and then stop. I will then be pasting the data into lots of different locations/wkshts/files. I'm looking for the code to replicate me manually selecting the two non contiguous areas and then copy them to the clipboard. When I then paste the two chunks of data they will be naturally joined one on top of the other.

 

[Joe4]

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)?