Combining several lists (each their own named range) into a master list

[GuyGadois]

I would like to combine several lists (each their own named range) into a master list. Each list has the same number of columns but vary in length - 4. The named range may also have some blank rows in them and I don't want to bring those over to the master list. Is it possible to do this?

Cheers,

GG

 

[My Aswer Is This]

This can surely be done but we need specific details.
Like what are the names of these Named ranges and where do you want them combined.
And please define "List" I understand Named Range but not sure what "List" means.

 

[GuyGadois]

This can surely be done but we need specific details.
Like what are the names of these Named ranges and where do you want them combined.
And please define "List" I understand Named Range but not sure what "List" means.

Sorry about the confusion. The named ranges are Export_CAAP, Export_DSIP, and Export_Alpha. Each named range contains the 4 columns and each column has the same category of data for each one column (for example each named range contains "Product","Allocation","Cost" and "Details". Each named range has some blanks (always at the end, never in the middle of the range). I would like them combined on the same worksheet all the other named ranges appear but if needed it could be it's own worksheet. Can this be done dynamically or does someone need to manually engage a filter? I prefer dynamically generated if possible.

Cheers,

GG

 

[JoeMo]

Since you didn't tell us where you wanted the combined list on the worksheet with the named ranges, this puts the combined list on a new sheet called "Combined" that the code creates. Assumes your named ranges are filled with constants (not formulas) except for blank rows.

Sub CombineNamedRanges()
Dim R1 As Range, R2 As Range, R3 As Range, nxRw As Long
Set R1 = Range("Export_CAAP")
Set R2 = Range("Export_DSIP")
Set R3 = Range("Export_Alpha")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("Combined").Delete
On Error GoTo 0
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "Combined"
On Error Resume Next
R1.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A1")
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R2.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R3.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
With Application
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[GuyGadois]

Joe, thanks for the reply. The ranges are all filled with formulas so the transfer would have to be values if possible. Would it be possible to paste the values on a new worksheet?

Cheers,

Guy

Since you didn't tell us where you wanted the combined list on the worksheet with the named ranges, this puts the combined list on a new sheet called "Combined" that the code creates. Assumes your named ranges are filled with constants (not formulas) except for blank rows.

Sub CombineNamedRanges()
Dim R1 As Range, R2 As Range, R3 As Range, nxRw As Long
Set R1 = Range("Export_CAAP")
Set R2 = Range("Export_DSIP")
Set R3 = Range("Export_Alpha")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("Combined").Delete
On Error GoTo 0
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "Combined"
On Error Resume Next
R1.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A1")
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R2.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R3.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
With Application
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[JoeMo]

Are the values numbers or text, or a mixture of both? Are the "blank" cells formula cells that are returning "", or are they truly blank?

 

[GuyGadois]

Are the values numbers or text, or a mixture of both? Are the "blank" cells formula cells that are returning "", or are they truly blank?

JoeMo, they are a mixture of text,currency and percentage. The cells that are black are actually returning “”.

Thanks,

Guy

 

[JoeMo]

See if this works for you.

Sub CombineNamedRanges()
Dim R1 As Range, R2 As Range, R3 As Range, nxRw As Long
Set R1 = Range("Export_CAAP")
Set R2 = Range("Export_DSIP")
Set R3 = Range("Export_Alpha")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("Combined").Delete
On Error GoTo 0
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "Combined"
On Error Resume Next
R1.SpecialCells(xlCellTypeFormulas).Copy
Sheets("Combined").Range("A1").PasteSpecial xlValues
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R2.SpecialCells(xlCellTypeFormulas).Copy
Sheets("Combined").Range("A" & nxRw).PasteSpecial xlValues
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R3.SpecialCells(xlCellTypeFormulas).Copy
Sheets("Combined").Range("A" & nxRw).PasteSpecial xlValues
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row
For i = Sheets("Combined").Range("A1:A" & nxRw).Rows.Count To 1 Step -1
    If Sheets("Combined").Cells(i, "A").Value = "" Then Sheets("Combined").Cells(i, "A").EntireRow.Delete
Next i
With Application
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[Shetara]

Since you didn't tell us where you wanted the combined list on the worksheet with the named ranges, this puts the combined list on a new sheet called "Combined" that the code creates. Assumes your named ranges are filled with constants (not formulas) except for blank rows.

Sub CombineNamedRanges()
Dim R1 As Range, R2 As Range, R3 As Range, nxRw As Long
Set R1 = Range("Export_CAAP")
Set R2 = Range("Export_DSIP")
Set R3 = Range("Export_Alpha")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("Combined").Delete
On Error GoTo 0
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "Combined"
On Error Resume Next
R1.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A1")
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R2.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
nxRw = Sheets("combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
R3.SpecialCells(xlCellTypeConstants).Copy Sheets("Combined").Range("A" & nxRw)
With Application
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

Hello I know this post is old but I copied this Macro code into my sheet and am receiving a debug error. Of course I renamed the sheets to match mine, I also have R1 - R7. I am trying to combine 7 sheets into a master list.
Sheet names:
- FAB1, FAB2,FAB3...
Can you please help?

 

[JoeMo]

Post the exact code you are using, tell us what line causes the error and exactly what the error message is.