Combining multiple Named Ranges into one

[plasteredric]

Hi All, i've been trying to combine multiple named ranges into one, but have so far not been able to get it to work. I've tried using the Union function but kept throwing up errors (probably didn't write the code correctly)

I have a load of data in sheet "RTR" with various named ranges, "Home_Details" (C3:F100) and "Rate_Details" (K3:L100) are the ones I want to combine and then copy/paste into another sheet starting at cell B2.

I'll be adding more ranges further down the line but for now these are the only ones I want to combine.

Would there be issues if the ranges were not the same number of rows?

Any help appreciated

[mana]

Option Explicit


Sub test()
    Dim n as Name
    
    For Each n In ActiveWorkbook.Names
        If n.RefersToRange.Worksheet.Name = "RTR" Then
            n.RefersToRange.Copy Sheets("Combine").Range("a" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
    
End Sub

マナ

[plasteredric]

Thanks for the help,
i've tried that code and it works for getting the ranges, however it puts them in the same column. I'd like it to put the ranges next to each other so that "Home_Details" (C3:F100) is put into cells B2:E99 and "Rate_Details" (K3:L100) is put into cells F2:G99.
Also, it appears that the code selects all the named ranges with the sheet? but I have other named ranges that I don't want to copy as part of this function.

Thanks

Option Explicit


Sub test()
    Dim n as Name
    
    For Each n In ActiveWorkbook.Names
        If n.RefersToRange.Worksheet.Name = "RTR" Then
            n.RefersToRange.Copy Sheets("Combine").Range("a" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
    
End Sub

マナ

[mdmackillop]

I don't understand the reluctance to post a workbook with sample data showing exactly what you are after.

[mana]

???

Option Explicit


Sub test2()
    Dim n
    
    For Each n In Array("Home_Details", "Rate_Details")
        Range(n).Copy Sheets("Combine").Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
    
End Sub

マナ

[plasteredric]

That works however if there is data in the cells then it pastes the data in the next available column, I need it to overwrite any data/formatting that is already there.
I've put together a sample sheet showing what I need, this may or may not help. Note the cells & ranges are named different to what I put in my initial question.
Daily new NEW BACKUP.xlsm

???

Option Explicit


Sub test2()
    Dim n
    
    For Each n In Array("Home_Details", "Rate_Details")
        Range(n).Copy Sheets("Combine").Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
    
End Sub

マナ

[mdmackillop]

Sub Test()
    Dim Tgt As Range
    Set Tgt = Sheets("Combined").Range("Q4")  
    Tgt.CurrentRegion.Clear   'Define range to clear as required
    With Sheets("RTR")
        .Range("PER_NAMES").Copy Tgt
        .Range("TRAIN_NAME").Copy Tgt.Offset(, .Range("PER_NAMES").Columns.Count)
    End With
End Sub

[mana]

Union(Range("PER_NAMES"), Range("TRAIN_NAME")).Copy Sheets("Combined").Range("Q4")

[plasteredric]

Thanks mana, works great