Need help with copying worksheets and creating pivot tables/charts in new workbooks using Macro

[tazeo]

I've been working on a report that I clean and collate using PowerQuery, and it's been fantastic.

However, I'm facing a slight issue when it comes to taking the output (which has been split into worksheets, with one worksheet for each work group) and creating a new workbook for each sheet. My current code copies the worksheet, but it stays linked back to the PowerQuery which might be causing the problem.

The main challenge is adding a pivot table and pivot chart to each workbook. The split code works perfectly, but I'm struggling with the pivot table/chart part. If anyone can help, I'd really appreciate it! I've spent most of last weekend trying to figure this out, and now it's become an OCD thing for me. @James006 (in another question) suggested I try positing my entire code to see if anyone can help.

My wishlist is:

1. Copy each sheet from an existing workbook to a new sheet in the new workbook. The names of the sheets in the existing workbook should match the names of the new sheets in the new workbook.
2. Change the data on each sheet to a table, using the default format.
3. Create a pivot table on a new sheet in the workbook with the following fields:
   1. Rows: "Functional Location"
   2. Columns: "Incident Classification"
   3. Values: Count of "Incident ID"
   4. Filters: "Event Type" and "Incident Status"
4. Create a pivot chart on a new sheet using the same data and variables as the pivot table - same sheet would be nice but I think this was causing a problem..
5. Repeat the above steps for each sheet in the existing workbook.

My current code, though this is probably the 20th version as I chopped and changed trying to fix different parts.

Sub SheetsToWorkbooksAndPivot()
    ' Declare all the variables
    Dim ws As Worksheet
    Dim folderPath As String
    
    ' Prompt the user to select a folder to save the output files
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder to save the output files"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        If .SelectedItems.Count = 0 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With
    
    ' Turn screen updating off to speed up your macro code
    Application.ScreenUpdating = False
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Copy the target sheet to the new workbook
        ws.Copy
        
        ' Save the new workbook with sheet name
        ActiveWorkbook.SaveAs folderPath & "\" & ws.Name
        
        ' Create a pivot table on a new sheet
        Dim newSheet As Worksheet
        Set newSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        Dim pivotCache As pivotCache
        Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, ThisWorkbook.Worksheets(1).ListObjects("Table1"))
        Dim pivotTable As pivotTable
        Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=newSheet.Range("A3"), TableName:="PivotTable1")
        pivotTable.PivotFields("Functional Location").Orientation = xlRowField
        pivotTable.PivotFields("Incident Classification").Orientation = xlColumnField
        pivotTable.AddDataField pivotTable.PivotFields("Incident ID"), "Count of Incident ID", xlCount
        
        pivotTable.PivotFields("Event Type").Orientation = xlPageField
        pivotTable.PivotFields("Incident Status").Orientation = xlPageField
        
       
        ' Close the new workbook
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
    
    ' Turn screen updating on
    Application.ScreenUpdating = True
End Sub