Join 2 sheets into one

[virtuosok]

Hi, I have 2 tabs within the same Excel workbook. Both tabs have exactly the same structure (i.e. number of columns and their headers), while the data itself is slightly different. These two tabs are getting updated independently on an ongoing basis.
Is there a way to generate a formula which would be combining the output from both sheets onto a new sheet in the existing workbook? As a one-off solution, copy/paste works just as well but it's not sustainable long term
My ranges are columns A-V and rows 12-50000 on both tabs (currently either tab has about 300 entries but this is growing fast). A related concern is to try and avoid empty rows in the middle of a new worksheet when joining 2 sheets... any advice appreciated!

 

[sandy666]

try Power Query (Get&Transform)
change your source ranges to Excel tables (Ctrl+T) then load both to the Power Query Editor then use Append feature (Table.Combine) and load result to the third sheet

 

[virtuosok]

Thanks, this works, but is there a way to have third sheet auto-update whenever either of the first two sheets are changed?

 

[sandy666]

you can try with this in sheets module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

or define refresh time in Connections - Properties - Refresh every (set how often you want to refresh in minutes)

 

[virtuosok]

.......

Apologies one more question. I can't seem to be able to query worksheets from already open workbook, this defaults to the workbook stored on the computer. What am I doing wrong?

 

[sandy666]

I don't understand
You have two tables (Excel Tables) so use From Table for each source and it will be loaded into Power Query Editor then Append

I have 2 tabs within the same Excel workbook. Both tabs have exactly the same structure

if you want to use From Workbook, your working file must be saved first