So I have managed to get some code which looks very promising:

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub
However there are a couple of issues. The main one is that to the right of the connected date, I created a data set linked to the imported set. This set is much smaller and organises the data into the same columns across the sheets.

However as this data is linked it does not display when carrying out the macro.

The other changes I am looking for are:

When the source data updates, it automatically updates on the combined sheet - it needs to do this without deleting the sheet as there will be other cells and calculations referencing the data.

Set to auto-total (group) whenever there is new data on a specific column.

Would really appreciate any help.

Cheers