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.