Copy/paste non-contiguous named ranges with VBA

mosquitoEEK

New Member
Joined
Jan 29, 2012
Messages
13
Hi all,

Any suggestions for how to copy and paste non-contiguous named ranges from one workbook to another via VBA? For purposes of this question, I'm working with three columns as named ranges - Product, Country, and Region. They are in columns A, C, and E.

The only code I am working from is the one I had when I did not have columns A, C, and E as named ranges, only as static cell references. Please let me know if I can provide any other information.

Thanks in advance!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi all,

Any suggestions for how to copy and paste non-contiguous named ranges from one workbook to another via VBA? For purposes of this question, I'm working with three columns as named ranges - Product, Country, and Region. They are in columns A, C, and E.

The only code I am working from is the one I had when I did not have columns A, C, and E as named ranges, only as static cell references. Please let me know if I can provide any other information.

Thanks in advance!
Here's some basic code you can adapt:
Code:
Sub NonContiguousRanges()
Dim P As Range, C As Range, R As Range, Rng As Range

Set P = Range("Product")
Set C = Range("Country")
Set R = Range("Region")
Set Rng = Union(P, C, R) 'Non-contiguous range with three Areas

For i = 1 To Rng.Areas.Count
    With Rng.Areas(i)
        'your code for copy/paste this area here
    End With
Next i
End Sub
 
Upvote 0
Thanks - unfortunately I am not too well-versed in code so that brings up another question. My code, using your suggestions above, is as follows:

HTML:
Dim P As Range, C As Range, R As Range, Rng As Range

Set P = Range("Product")
Set C = Range("Country")
Set R = Range("Region")
Set Rng = Union(P, C, R) 'Non-contiguous range with three Areas

For i = 1 To Rng.Areas.Count
    With Rng.Areas(i)
        Range("Region").AutoFilter Field:=Range("Region").Column, Criteria1:="South"
        Range("Status").AutoFilter Field:=Range("Status").Column, Criteria1:="Open"
        ActiveSheet.Range("Rng").Select
        Selection.Copy
    End With
Next i
So I have set Rng to be named ranges P, C, and R; the code then autofilters two columns that I want it to filter, but then I am getting an "Application-defined or object-defined error" on the row that says "ActiveSheet.Range("Rng").Select. Suggestions?
 
Upvote 0
Rng is already defined as the union of ranges P,C and R. There is no named range "Rng" on the worksheet so Excel doesn't recognize Range("Rng") and throws the error. Try replacing ActiveSheet.Range("Rng").Select with
Rng.select
 
Upvote 0
Thanks, I didn't get the error when I made that change. Sorry for all the questions, but can you explain what this means and why my code needs it?:


HTML:
For i = 1 To Rng.Areas.Count     With Rng.Areas(i)
</pre>Thanks!
 
Upvote 0
Thanks, I didn't get the error when I made that change. Sorry for all the questions, but can you explain what this means and why my code needs it?:


HTML:
For i = 1 To Rng.Areas.Count     With Rng.Areas(i)
Thanks!
When you create a range from non-contiguous ranges, each contiguous segment within that range is an Area of that range. There are some procedures that Excel cannot perform in one shot on non-contiguous ranges, but can be performed sequentially on one area of the range at a time. In the statement you cited, Rng.Areas.Count is the number of Areas that Rng includes. Each area has an index which defines it; so Area(1) is the first area and so on. So the statement simply steps through the areas one at a time and the With Area(i) then says with this area perform the following actions.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top