Combine Dynamic Worksheets Into One Sheet

[mk1103]

I have two sheets in this sample workbook attached and need to have the data from the Pending sheet and the Completed sheet to be combined into a third sheet in the same workbook. They have the same column headers and need a macro to combine them even as data is continuously added to both sheets as time goes on. I would greatly appreciate your help.

[Dave Hawley]

Create 2 Dynamic named ranges, then use;

Sub CombineEm()

With Sheet3 'CodeName
    .UsedRange.Clear
    Range("DynaRange1").Copy .Range("A1")
    Range("DynaRange2").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
End With
    

End Sub

[mk1103]

Sorry I'm new to this. By naming 2 dynamic ranges do you mean insert and define the ranges for both the pending and completed sheets? I did that and then copied the code into sheet 3 but the macro did not work. Any suggestions? Thank you.

[Trebor76]

Hi there,

Let me know if the following macro does the trick (you'll have to create a tab called "Combined" first though):

Sub CombineII()

Dim lngLastRow As Long

lngLastRow = Sheets("Combined").Range("A65536").End(xlUp).Row

    If lngLastRow > 1 Then
    Sheets("Combined").Range("A1:Q" & lngLastRow).ClearContents
    End If

lngLastRow = Sheets("Pending").Range("A65536").End(xlUp).Row

Sheets("Pending").Range("A1:Q" & lngLastRow).Copy Sheets("Combined").Range("A1")

lngLastRow = Sheets("Completed").Range("A65536").End(xlUp).Row
Sheets("Completed").Range("A2:Q" & lngLastRow).Copy
Sheets("Combined").Select

Range("A65536").End(xlUp).Offset(1, 0).Select

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

HTH

Robert

[Dave Hawley]

It works fine and to create a dynamic named range, hover over dynamic. The code does NOT go into a Sheet, it goes into a standard module. That is, insert>Name>Define.

[mk1103]

I've copied your code into the module attached in this sheet and for some reason nothing happens as I enable the macros...unless there is something else I'm doing wrong...

[Dave Hawley]

DynaRange1 defined as;
=OFFSET(Pending!$A$2,0,0,COUNTA(Pending!$A:$A)-1,COUNTA(Pending!$1:$1))

DynaRange1 defined as;
=OFFSET(Completed!$A$2,0,0,COUNTA(Completed!$A:$A)-1,COUNTA(Completed!$1:$1))

Then run;

Sub CombineEm()
     
    With Sheet4 ' CodeName
        .UsedRange.Clear
        Range("DynaRange1").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
        Range("DynaRange2").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
    End With
     
     
End Sub

[mk1103]

Sorry I keep getting an error...is there any way that you could attach the spreadsheet that is working properly to the thread? Thank you for your help.

[Trebor76]

Hi there,

To use my macro you'll either have to change all references to "Combined" to "Master" in the code or name the third tab as "Combined" instead of "Master".

Have a look at the attached which is based on your original example.

HTH

Robert

[Dave Hawley]

See working example.