VBA code to help split PQ result

[Radoslaw Poprawski]

I have PQ query that works with VBA.

Currently this code:

Sub CreateQueries()

Set dict = CreateObject("scripting.dictionary")

For Each kom In ThisWorkbook.Worksheets("Base_Data").ListObjects("Table1").ListColumns(1).DataBodyRange
   IsEmpty dict(kom.Value & kom.Offset(0, 1).Value)
Next

For i = 0 To dict.Count - 1
   With ThisWorkbook.Queries
      nazwa = "CostCenter_" & i
      .Add Name:=nazwa, Formula:="let Zródlo = funkcja(" & i & ") in Zródlo"
   End With
   
   Set sh = ThisWorkbook.Worksheets.Add(After:=ActiveSheet)
   sh.Name = nazwa
       
   With sh.ListObjects.Add(SourceType:=0, Source:= _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & nazwa _
      , Destination:=Range("$A$1")).QueryTable
      .CommandType = xlCmdSql
      .CommandText = Array("SELECT * FROM [" & nazwa & "]")
      .BackgroundQuery = False
      .RefreshStyle = xlOverwriteCells
      .AdjustColumnWidth = False
      .ListObject.DisplayName = nazwa
      .Refresh BackgroundQuery:=False
   End With
Next

End Sub

Currently, it splits into different sheets by cost center.

I have no idea how to make this code split the result into workbooks, where each workbook is one location with all Cost centers sheets assigned to that location.

The expected result is this: Each file is named after the location. in each file, I should get separate sheets with cost centers that belong to it.

Here is a link to the whole file: custom_Function_PQ.xlsm

 

[Radoslaw Poprawski]

my post dropped quite low on the list, thats why I'm bringing it up so it wont get forgotten.