Hi ya'll. I've come across an issue with no clear answer anywhere on the forum.

Context: I have a sheet called 'Imported Data' that contains thousands of rows of data & 15 columns. It will always contain the same 15 columns - the data comes from a | delimited text file. Through other macros import the data, format it as a table, and create one new sheet for every unique value in the "Edit Type" tab (usually 4-8 sheets). Then a macro sorts the table by the applicable edit type, moves all that data to the corresponding sheet, and loops until it hits a sheet that is always last - 'Programming'.

For example this sample table on the 'Imported Table' sheet would generate 3 sheets, "A", "B", and "C" - each with the row(s) matching to that edit type, as well as the headers:

Column1 Column2 Edit Type Columns 4 - 14 Column15
Hello world A ... and
Hola mundo B ... all
Hallo welt A ... who
Hallo wereld B ... inhabit
Helo byd C ... it



After all the data has been filtered onto the applicable sheets, I attempt to create another sheet with a pivot table based on the data. Therefore, the end document would have 8 sheets - 'Imported Data'
'A,' 'B,' 'C,'
'A Pivot,' 'B Pivot,' 'C Pivot,'
and 'Programming'

My problem is generating the pivot tables - some of the edit type sheets throw errors

What I have:
Sub pivots()

Dim pivsht As Worksheet
Dim datasht As Worksheet
Dim pivcache As PivotCache
Dim pivtable As PivotTable
Dim pivrange As Range
Dim lastrow As Long
Dim lastcol As Long
Dim shtName As String
Dim shtIndex As Long
Dim namelength As Integer


shtIndex = 2


Do Until shtName = "Programming"
    shtName = Sheets(shtIndex).Name
    If shtName = "Programming" Then
        Exit Do
    End If
    
    Sheets(shtIndex).Activate
    namelength = Len(ActiveSheet.Name)
    
    If namelength >= 25 Then ActiveSheet.Name = Left(ActiveSheet.Name, 25)
    shtName = ActiveSheet.Name
    
    'Ensure there are no existing tabs called "PivotTable"
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("PivotTable").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = shtName & " Pivot"
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'Set which sheet has the data and which sheet the data goes to
    Set pivsht = Worksheets(shtName & " Pivot")
    Set datasht = Worksheets(shtName)
    
    'Gets range for all data on datasht
    lastrow = datasht.Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = datasht.Cells(1, Columns.Count).End(xlToLeft).Column
    Set pivrange = datasht.Cells(1, 1).Resize(lastrow, lastcol)
    
    'Sets pivot cache (for creating pivot table)
    Set pivcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(pivrange.Address))
    
    'Sets pivot table (THIS IS CAUSING ERRORS)
    Set pivtable = pivcache.CreatePivotTable _
        (Tabledestination:=pivsht.Cells(1, 1), _
        TableName:="PivTable")

    '.....The rest of my code below, all formatting of pivot tables.

   End Sub
Before adding the line "On Error GoTo 0," it would generate a pivot table for B and C, but give a completely blank sheet 'A Pivot' with no pivot table. After adding the line, it no longer skips A and goes to B, and it hits an error at the Set pivtable line. The error reads:
qPsFP5J.png

I've researched the heck out of this error and all I have found is people stating that it is due to headers or columns being blank. None of the data is missing - what else could be causing this error? I've tried assigning random names to TableName ( "PivTable" & Rnd() ) in an attempt to give it a unique name, that resulted in the same error.

Any thoughts?