How to automate a power query in VBA?

It would be easier to set your query up using the built-in tools, not VBA. It is NOT that you can't, as it seems at least one other answerer has misunderstood my point, it is just easier with the in-built, optimized, tools, and a lot faster to alter than continually copying your data elsewhere, escaping "", tracking back M language errors etc. You can then run that query via VBA.

You load your data via the appropriate method which can be from file, looping files in a folder, web, database.... the list goes on. You can import from external sources as well as load from internal. Have a look here for more information on loading from external sources.

Once you have secured your source and it is loaded you will be presented with the query editor where you can perform your transformation steps.

The point being that as you perform your steps using the UI, M code is written in the background and forms the basis of a re-usable query provided you don't change the source format or location. If you do, it is an easy edit to update the appropriate data source in the UI.

In your case, when you have performed your steps and have a query as you wish you then close and load to sheet2.

At this step, the first time you are setting this up you will select sheet 2 as your close and load destination:

NB: When you select existing sheet, ensure Sheet 2 already exists and you can manually edit Sheet2! in front of the suggested range.

You are experiencing issues because you keep trying to recreate all of this with code.

Don't. Set it up using the UI and load to sheet2. From then on, either open the query editor to edit the steps and/or refresh the query to load the existing sheet2 with new/refreshed data.

Some of the available methods for refreshing your query:

The query will be refreshed by VBA/Manual refreshes to the sheet it resides in (Sheet2), or to the workbook itself e.g. Sheet2.Calculate , ThisWorkbook.RefreshAll, manually pressing the refresh workbook button in the data tab (these are all overkill really)

More targeted methods:

VBA for the query table in sheet 2:

ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False   

Change the above to the appropriate table etc.

Right clicking in the querytable itself and selecting refresh:

Click on the refresh button in the workbook queries window on the right hand side for the query in question (icon with green circling arrows)

The Ken Pulls VBA way (minor edit from me)

Option Explicit
Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)

    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn
    On Error GoTo 0
End Sub

There shouldn't be any real need for you to do all of this work via VBA. You may have some tricky data manipulation you feel more comfortable doing with VBA and then having PowerQuery access that processed data as source. You can fire off the whole lot by having a subroutine that calls the processing routine and then uses one of the VBA command methods listed above. There are more methods and I will add them when I have more time.

Calculations:

If you have calculations that depend on the PowerQuery output you have 4 obvious immediate options:

1. Add these calculations where possible into PowerQuery. It supports calculated columns, user defined functions and lots more.
2. Add the PowerQuery output to the data model and use the data model to perform calculations including calculated fields. This will give you access to time intelligence functions as well.
3. Use VBA to add the calculations to the appropriate areas in sheet 2 if the range changes on refresh
4. If range doesn't change on refresh simply put your formulas out of the way.

VBA is absolutely suitable for automating PowerQuery and is particularly efficient for repetitive work. The trick is to create first the query you need in PowerQuery, then use the Advanced Editor to capture the M. Copy it and store it, either in a cell in the workbook, or in a separate text file.

The method is described in detail by Gil Raviv. For convenience, I store my M in text files instead of the workbook and load it with:

Function LoadTextFile(FullFileName As String) As String
  With CreateObject("Scripting.FileSystemObject")
    LoadTextFile = .OpenTextFile(FullFileName, 1).readall
  End With 
End Function

The nice thing about text files is that they are independent of excel and can re-used by many workbooks.

Here is some M:

let
// load the reference file (variables are shown in capitals;  
// variable values are replaced with strings from the excel control workbook)
    Source = Excel.Workbook(File.Contents(PATH_AND_NAME), null, true),
    ImportSheet = Source{[Item=SHEET_NAME,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ImportSheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACCOUNT", type text}})
in
    #"Changed Type"

Once loaded into VBA (from either workbook or text file), the M can be edited within VBA, for example by substituting placeholder words, or by using the M command names to locate and alter lines if necessary, e.g.

    ' create the M script to read the M file that will do the import
        M_Script = LoadTextFile(M_Source)
        
    ' insert the path
        M_Script = Replace(M_Script, "PATH_AND_NAME", """" & qSource & """") 
    
    ' insert the worksheet name
        If wksName <> "" Then M_Script = Replace(M_Script, "SHEET_NAME", """" & wksName & """")
        

The next step is to load the query. I do this using the technique described by Gil as follows:

Dim qry As WorkbookQuery

If DoesQueryExist(qName) Then 
    ' Deleting the query 
    Set qry = ThisWorkbook.Queries(qName) 
    qry.Delete 
End If 
           
Set qry = w.queries.Add(qName, M_Script, qSource)
                      
' We check if data should be loaded to Data Model 
shouldLoadToDataModel = ThisWorkbook.Worksheets(1).Cells(13, "D") 
 
' We check if data should be loaded to worksheet 
shouldLoadToWorksheet = ThisWorkbook.Worksheets(1).Cells(13, "E") 
 
If shouldLoadToWorksheet Then 
    ' We add a new worksheet with the same name as the Power Query query 
    Set currentSheet = Sheets.Add(After:=ActiveSheet) 
    currentSheet.Name = qName 
 
    If Not shouldLoadToDataModel Then 
        ' Let's load to worksheet only 
        LoadToWorksheetOnly qry, currentSheet 
    Else 
        ' Let's load to worksheet and Data Model 
        LoadToWorksheetAndModel qry, currentSheet 
    End If 
ElseIf shouldLoadToDataModel Then 
    ' No need to load to worksheet, only Data Model 
    LoadToDataModel qry 
End If 

Here is the LoadToDataModel function:

Option Explicit
Function LoadToDataModel(w As Workbook, query As WorkbookQuery, error As Integer) As Boolean
On Error GoTo Load_Error
    ' This code loads the query to the Data Model
    w.Connections.Add2 "Query - " & query.Name, _
        "Connection to the '" & query.Name & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , """" & query.Name & """", 6, True, False

    LoadToDataModel = True
    
Load_Exit:
    Exit Function
    
Load_Error:
    LoadToDataModel = False
    error = Err.Number
    Resume Load_Exit
End Function

And the function for LoadToWorksheetOnly (thanks to wayback machine):

Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet) 
    ' The usual VBA code to create ListObject with a Query Table 
    ' The interface is not new, but looks how simple is the conneciton string of Power Query: 
    ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name 
     
    With currentSheet.ListObjects.Add(SourceType:=0, Source:= _ 
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ 
        , Destination:=Range("$A$1")).QueryTable 
        .CommandType = xlCmdDefault 
        .CommandText = Array("SELECT * FROM [" & query.Name & "]") 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .PreserveColumnInfo = False 
        .Refresh BackgroundQuery:=False 
    End With 
     
End Sub 

Gil's code allows for importing data to either the data model or a worksheet. The OP requires the second, and if the method is followed, the transformed data should appear in the worksheet.