VBA & PQ - Merge All Excel Sheets

Sub Load_Power_Query()
'=====================================
'Created by Harsh C
'=====================================
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim qryName As String
    Dim formula As String
    Dim counter As Integer
 
    ' Set reference to the current workbook
    Set wb = ActiveWorkbook
 
    ' Counter for naming queries if table name is not suitable
    counter = 1
 
    ' Loop through each worksheet in the workbook
    For Each ws In wb.Worksheets
        ' Check if worksheet contains tables
        If ws.ListObjects.Count > 0 Then
            ' Loop through each table in the worksheet
            For Each tbl In ws.ListObjects
                ' Create a unique name for the query based on the table name
                qryName = "Query_" & Replace(tbl.Name, " ", "_")
 
                ' Create the M formula with proper syntax
                formula = "let" & vbNewLine & _
                          "    Source = Excel.CurrentWorkbook()," & vbNewLine & _
                          "    #""" & tbl.Name & """ = Source{[Name=""" & tbl.Name & """]}[Content]" & vbNewLine & _
                          "in" & vbNewLine & _
                          "    #""" & tbl.Name & """"
 
                ' Try to add the query, if it already exists, append a counter to the name
                On Error Resume Next
                wb.Queries.Add qryName, formula
 
                If Err.Number <> 0 Then
                    Err.Clear
                    qryName = "Query_" & Replace(tbl.Name, " ", "_") & "_" & counter
                    counter = counter + 1
                    wb.Queries.Add qryName, formula
                End If
                On Error GoTo 0
 
                Debug.Print "Added query: " & qryName & " for table " & tbl.Name & " in " & ws.Name
            Next tbl
        End If
    Next ws
 
    MsgBox "All tables have been loaded into Power Query. Open the Power Query Editor to view them.", vbInformation
End Sub

Sub Merge_Combine()
'=====================================
'Created by Harsh C
'=====================================
 
    Dim ws As Worksheet
    Dim combinedSheet As Worksheet
    Dim lastRow As Long
    Dim combinedRow As Long
    Dim colName As String
    Dim colNumber As Long
    Dim wsRange As Range
    Dim userResponse As VbMsgBoxResult
    Dim headingRange As Range
    Dim finalDumpSheet As Worksheet
    Dim colHeader As Range
 
    ' Reference active workbook
    Dim wb As Workbook
    Set wb = ActiveWorkbook
 
    ' Ask user for merging option
    userResponse = MsgBox("Do you want to merge all sheets?", vbYesNo + vbQuestion, "Merge Option")
 
    If userResponse = vbYes Then
        Set combinedSheet = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        combinedSheet.Name = "CombinedData"
        combinedSheet.Cells(1, 1).Value = "Sheet Name"
        combinedRow = 2
        colName = InputBox("Enter column letter to check last row:", "Column Name Input")
 
        On Error Resume Next
        colNumber = Range(colName & "1").Column
        On Error GoTo 0
 
        If colNumber <= 0 Then
            MsgBox "Invalid column letter.", vbExclamation
            Exit Sub
        End If
 
        For Each ws In wb.Worksheets
            If ws.Name <> combinedSheet.Name Then
                combinedSheet.Cells(combinedRow, 1).Value = ws.Name
                lastRow = ws.Cells(ws.Rows.Count, colNumber).End(xlUp).Row
 
                If lastRow > 0 Then
                    Set wsRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, ws.UsedRange.Columns.Count))
                    wsRange.Copy Destination:=combinedSheet.Range("B" & combinedRow)
                    combinedRow = combinedRow + lastRow
                End If
                combinedRow = combinedRow + 1
            End If
        Next ws
 
        MsgBox "All sheets merged successfully!", vbInformation
    Else
        Set headingRange = Application.InputBox("Select heading range:", Type:=8)
        If headingRange Is Nothing Then Exit Sub
 
        Set finalDumpSheet = wb.Sheets("Selective Headings")
        If finalDumpSheet Is Nothing Then
            MsgBox "Sheet 'Selective Headings' not found.", vbExclamation
            Exit Sub
        End If
 
        combinedRow = 2
        For Each ws In wb.Worksheets
            If ws.Name <> "Final Dump" Then
                lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
                For Each colHeader In headingRange
                    colNumber = Application.Match(colHeader.Value, ws.Rows(1), 0)
                    If Not IsError(colNumber) Then
                        Set wsRange = ws.Range(ws.Cells(2, colNumber), ws.Cells(lastRow, colNumber))
                        wsRange.Copy Destination:=finalDumpSheet.Cells(combinedRow, colHeader.Column)
                    End If
                Next colHeader
                combinedRow = combinedRow + lastRow
            End If
        Next ws
 
        MsgBox "Selected content merged into 'Final Dump' successfully!", vbInformation
    End If
End Sub

=VSTACK(Data_1!A.:.A,Data_2!A.:.A,Data_3!A.:.A)