VBA Code for Excel Power Query - change

[Radoslaw Poprawski]

Hi

I have the following code:

Sub CreateQueries()
Application.ScreenUpdating = False
Set dict = CreateObject("scripting.dictionary")

For Each kom In ThisWorkbook.Worksheets("204B_Referenced").ListObjects("_204B_Referenced").ListColumns(6).DataBodyRange
   IsEmpty dict(kom.Value & kom.Offset(0, 6).Value)
Next

For i = 0 To dict.Count - 1
   With ThisWorkbook.Queries
      Name = "IT_Equpment_" & dict.Keys()(i)
      .Add Name:=Name, Formula:="let Source = ITSplitter(" & i & ") in Source"
   End With
   
   'Set sh = ThisWorkbook.Worksheets.Add(After:=ActiveSheet)
   Worksheets("template").Copy After:=ActiveSheet
   'Set sh = Sheets("Sheet4").Copy(After:=ActiveSheet)
   Set sh = ActiveSheet
   sh.Name = Name
       
   With sh.ListObjects.Add(SourceType:=0, Source:= _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Name _
      , Destination:=Range("$A$5")).QueryTable
      .CommandType = xlCmdSql
      .CommandText = Array("SELECT * FROM [" & Name & "]")
      .BackgroundQuery = False
      .RefreshStyle = xlOverwriteCells
      .AdjustColumnWidth = False
      .ListObject.DisplayName = Replace(Name, " ", "_", 1)
      .Refresh BackgroundQuery:=False
   End With
Next
Application.ScreenUpdating = True

End Sub

Im struggling with this line:

Set sh = ThisWorkbook.Worksheets.Add(After:=ActiveSheet)

Which I wish to change for

Set sh = Sheets("Sheet4").Copy(After:=ActiveSheet)

and for some reason that do not work.
Please help.

 

[Alex Blakenburg]

I have only seen that done in 2 steps.

    Sheets("Sheet4").Copy After:=ActiveSheet
    Set sh = ActiveSheet