Getting Power Query data to an Excel worksheet

Solution found for my situation:

After I create my blank sheet and have loaded the query, and the data is there as a connection only query.

Add this line:

LoadQuery "QueryName", ActiveSheet

QueryName is the name you used when creating the query.



Then have the following Private Sub

Private Sub LoadQuery(ByVal QueryName As String, ByVal LoadDataSheet As Worksheet)

    With LoadDataSheet.ListObjects.Add(SourceType:=0, Source:= _
                                     "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties=""""", _
                                     Destination:=LoadDataSheet.Range("$A$1")).QueryTable

        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & QueryName & "]")
   

        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_" & QueryName
        .Refresh BackgroundQuery:=False

    End With

End Sub

This will take all your data from the query and dump it into a sheet.
I cannot take credit for the solution, found on another site, but thought it might be helpful to others.