Macro of a Power Query

[melacy]

New to this - I think that's what I'm trying to do. Trying to create a macro of when I load a whole folder of data using "get data" and then edit the columns to create pivot chart. Running O365. This is Error I get.

This is the code, bolded section is what highlights yellow when I select "debug". I'm a novice which is probably obvious.

Sub Collated_All_Service_Summaries()
'
' Collated_All_Service_Summaries Macro
'

'
ActiveWorkbook.Queries.Add Name:="Transform Sample File", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(Parameter1,[Delimiter="","", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Promoted Headers"""
ActiveWorkbook.Queries.Add Name:="Parameter1", Formula:= _
"#""Sample File"" meta [IsParameterQuery=true, BinaryIdentifier=#""Sample File"", Type=""Binary"", IsParameterQueryRequired=true]"
ActiveWorkbook.Queries.Add Name:="Sample File", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Folder.Files(""C:\Users\marye\OneDrive - University of New Mexico Health Sciences Center\Division stuff\Service Reports\All Service Summary-individual"")," & Chr(13) & "" & Chr(10) & " Navigation1 = Source{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Navigation1"
ActiveWorkbook.Queries.Add Name:="Transform File", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = (Parameter1) => let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(Parameter1,[Delimiter="","", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & " in" & Chr(13) & "" & Chr(10) & " #""Promoted Headers""" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
ActiveWorkbook.Queries.Add Name:="All Service Summary-individual", Formula _
:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Folder.Files(""C:\Users\marye\OneDrive - University of New Mexico Health Sciences Center\Division stuff\Service Reports\All Service Summary-individual"")," & Chr(13) & "" & Chr(10) & " #""Filtered Hidden Files1"" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)," & Chr(13) & "" & Chr(10) & " #""Invoke Custom Function1"" = Table.AddColumn(#""Filtered Hidden Files1"", ""Transform File" & _
""", each #""Transform File""([Content]))," & Chr(13) & "" & Chr(10) & " #""Renamed Columns1"" = Table.RenameColumns(#""Invoke Custom Function1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & " #""Removed Other Columns1"" = Table.SelectColumns(#""Renamed Columns1"", {""Source.Name"", ""Transform File""})," & Chr(13) & "" & Chr(10) & " #""Expanded Table Column1"" = Table.ExpandTableColumn(#""Removed Other Columns1"", ""Transform" & _
" File"", Table.ColumnNames(#""Transform File""(#""Sample File"")))," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Expanded Table Column1"",{{""Source.Name"", type text}, {""SERVICE"", type text}, {""COUNT"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Workbooks("Book1").Connections.Add2 "Query - Transform Sample File", _
"Connection to the 'Transform Sample File' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform Sample File"";Extended Properties=""""" _
, "SELECT * FROM [Transform Sample File]", 2
Workbooks("Book1").Connections.Add2 "Query - Parameter1", _
"Connection to the 'Parameter1' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Parameter1;Extended Properties=""""" _
, "SELECT * FROM [Parameter1]", 2
Workbooks("Book1").Connections.Add2 "Query - Sample File", _
"Connection to the 'Sample File' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File"";Extended Properties=""""" _
, "SELECT * FROM [Sample File]", 2
Workbooks("Book1").Connections.Add2 "Query - Transform File", _
"Connection to the 'Transform File' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform File"";Extended Properties=""""" _
, "SELECT * FROM [Transform File]", 2
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""All Service Summary-individual"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [All Service Summary-individual]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "All_Service_Summary_individual"
.Refresh BackgroundQuery:=False
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("All_Service_Summary_individual[[#Headers],[Source.Name]]").Select
ActiveCell.FormulaR1C1 = "Date"
Columns("A:A").Select
Selection.TextToColumns Destination:=Range( _
"All_Service_Summary_individual[[#Headers],[Column2]]"), DataType:= _
xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(20, 5), Array(28, 9)), _
TrailingMinusNumbers:=True
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:C").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"All Service Summary-individual!R1C1:R1048576C3", Version:=7). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=7
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$3:$C$20")
Sheets("All Service Summary-individual").Select
Range("All_Service_Summary_individual[[#Headers],[Column2]]").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
Sheets("Sheet2").Select
ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
With ActiveChart.PivotLayout.PivotTable.PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.PivotFields("Date").AutoGroup
ActiveChart.PivotLayout.PivotTable.PivotFields("Quarters").Orientation = _
xlHidden
ActiveChart.PivotLayout.PivotTable.PivotFields("Years").Orientation = xlHidden
With ActiveChart.PivotLayout.PivotTable.PivotFields("SERVICE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("SERVICE")
.Orientation = xlPageField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("COUNT"), "Sum of COUNT", xlSum
With ActiveChart.PivotLayout.PivotTable.PivotFields("Sum of COUNT")
.Caption = "Count of COUNT"
.Function = xlCount
End With
Range("A4").Select
Selection.Ungroup
ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE")
.PivotItems(" ").Visible = False
.PivotItems("Burn").Visible = False
.PivotItems("Cardiac Cath Inpatient").Visible = False
.PivotItems("Cardiothoracic Surgery").Visible = False
.PivotItems("Cardiothoracic/Vascular Critical Care").Visible = False
.PivotItems("Carrie Tingley Inpatient Rehab").Visible = False
.PivotItems("Cerebrovascular").Visible = False
.PivotItems("Certified Nurse Midwife").Visible = False
.PivotItems("COVID-19 OB").Visible = False
.PivotItems("CTH Orthopedics").Visible = False
.PivotItems("Donor Services/Deceased").Visible = False
.PivotItems("Ear+Nose+Throat").Visible = False
.PivotItems("Emergency Department").Visible = False
.PivotItems("Emergency General Surgery").Visible = False
.PivotItems("Emergency Neurosurgery").Visible = False
.PivotItems("FM Team A").Visible = False
.PivotItems("FM Team B").Visible = False
.PivotItems("FP Maternal Child Health Service").Visible = False
.PivotItems("General Surgery").Visible = False
.PivotItems("Gynecology").Visible = False
.PivotItems("Gynecology Oncology").Visible = False
.PivotItems("Hematology Oncology").Visible = False
.PivotItems("L-D Triage").Visible = False
.PivotItems("Liver & Pancreas (HPB) Surgery").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE")
.PivotItems("Maternal Fetal Medicine").Visible = False
.PivotItems("Medical Oncology").Visible = False
.PivotItems("Medicine Blue").Visible = False
.PivotItems("Medicine Bronze").Visible = False
.PivotItems("Medicine Copper").Visible = False
.PivotItems("Medicine Critical Care").Visible = False
.PivotItems("Medicine Critical Care 2").Visible = False
.PivotItems("Medicine Gold").Visible = False
.PivotItems("Medicine Gray").Visible = False
.PivotItems("Medicine Nickel").Visible = False
.PivotItems("Medicine Orange").Visible = False
.PivotItems("Medicine Platinum").Visible = False
.PivotItems("Medicine Purple").Visible = False
.PivotItems("Medicine Red").Visible = False
.PivotItems("Medicine Silver").Visible = False
.PivotItems("Medicine Zinc").Visible = False
.PivotItems("Medicine Zinc 1A").Visible = False
.PivotItems("Medicine Zinc 2").Visible = False
.PivotItems("Medicine Zinc 2A").Visible = False
.PivotItems("Medicine Zinc 3").Visible = False
.PivotItems("Medicine Zinc 4").Visible = False
.PivotItems("Medicine Zinc 6").Visible = False
.PivotItems("Neonatology").Visible = False
.PivotItems("Neonatology Green").Visible = False
.PivotItems("Neonatology ICN4").Visible = False
.PivotItems("Neurohospitalist").Visible = False
.PivotItems("Neurology").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE")
.PivotItems("Neuroscience Critical Care").Visible = False
.PivotItems("Neurosurgery").Visible = False
.PivotItems("Newborn").Visible = False
.PivotItems("Newborn FP").Visible = False
.PivotItems("Obstetrics").Visible = False
.PivotItems("Obstetrics Jade").Visible = False
.PivotItems("Obstetrics Topaz").Visible = False
.PivotItems("Ophthalmology").Visible = False
.PivotItems("Orthopedics").Visible = False
.PivotItems("Pain Management").Visible = False
.PivotItems("Pediatric Continuity Clinic").Visible = False
.PivotItems("Pediatric Emergency Dept").Visible = False
.PivotItems("PEDS Critical Care").Visible = False
.PivotItems("PEDS CTH Acute").Visible = False
.PivotItems("PEDS CTH Rehab").Visible = False
.PivotItems("PEDS ENT").Visible = False
.PivotItems("PEDS Gastroenterology").Visible = False
.PivotItems("PEDS General").Visible = False
.PivotItems("PEDS Hematology Oncology").Visible = False
.PivotItems("PEDS Nephrology").Visible = False
.PivotItems("PEDS Neurology").Visible = False
.PivotItems("PEDS Neurosurgery").Visible = False
.PivotItems("PEDS Surgery").Visible = False
.PivotItems("PEDS Team Gecko").Visible = False
.PivotItems("PEDS Team Lobo").Visible = False
.PivotItems("PEDS Team Roadrunner").Visible = False
.PivotItems("PEDS Urology").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE")
.PivotItems("Plastic Surgery").Visible = False
.PivotItems("Surgical Critical Care").Visible = False
.PivotItems("Surgical Oncology").Visible = False
.PivotItems("Total:").Visible = False
.PivotItems("Transplant Surgery").Visible = False
.PivotItems("Trauma Azul").Visible = False
.PivotItems("Trauma Rojo").Visible = False
.PivotItems("Urogynecology").Visible = False
.PivotItems("Urology").Visible = False
.PivotItems("Vascular").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("SERVICE"). _
EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of COUNT")
.Caption = "Sum of COUNT"
.Function = xlSum
End With
End Sub