Results 1 to 7 of 7

Copy/paste non-contiguous named ranges with VBA

This is a discussion on Copy/paste non-contiguous named ranges with VBA within the Excel Questions forums, part of the Question Forums category; Hi all, Any suggestions for how to copy and paste non-contiguous named ranges from one workbook to another via VBA ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    13

    Default Copy/paste non-contiguous named ranges with VBA

    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!

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,688

    Default Re: Copy/paste non-contiguous named ranges with VBA

    Quote Originally Posted by mosquitoEEK View Post
    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

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    13

    Default Re: Copy/paste non-contiguous named ranges with VBA

    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 Code:
    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?

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,688

    Default Re: Copy/paste non-contiguous named ranges with VBA

    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

  5. #5
    New Member
    Join Date
    Jan 2012
    Posts
    13

    Default Re: Copy/paste non-contiguous named ranges with VBA

    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 Code:
    For i = 1 To Rng.Areas.Count     With Rng.Areas(i)
    Thanks!

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,688

    Default Re: Copy/paste non-contiguous named ranges with VBA

    Quote Originally Posted by mosquitoEEK View Post
    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 Code:
    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.

  7. #7
    New Member
    Join Date
    Jan 2012
    Posts
    13

    Default Re: Copy/paste non-contiguous named ranges with VBA

    Thank you! All of that information was very helpful.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com