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.
20525



???


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