Merge 2 sheets into 1

[JoeExcelHelp]

Gentleman, I think this is an easy ask
All the codes I've found seem to create a new WS when consolidating data.. which is not what I need

I have 2 worksheets (DataCollection, DataCollection2) both have the same data format and headers - (A1:CV1)
Data in both sheets start in row2 and the amount of possible rows are variable

I would like to merge data from both sheets above into a 3rd WS labeled (DataCombined) which has the same headers - (A1:CV1) and start the data import on row2
Each time the code is run, it clears existing data in (DataCombined) and then imports data from sheets (DataCollection, DataCollection2)

Thank You

[HansV]

Try this macro:

Code: Select all

Sub Merge123()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim m As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set ws1 = Worksheets("DataCollection")
    Set ws2 = Worksheets("DataCollection2")
    Set ws3 = Worksheets("DataCombined")
    ws3.Range("2:" & ws3.Rows.Count).ClearContents
    t = 2
    m = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws1.Range("A2:CV" & m).Copy Destination:=ws3.Range("A" & t)
    t = t + m - 1
    m = ws2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws2.Range("A2:CV" & m).Copy Destination:=ws3.Range("A" & t)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

[JoeExcelHelp]

Amazing.. Thank You Hans