Pivot Table Name is Not Valid Error - no missing data/columns

[AlecN96]

Hi ya'll, first time poster here! I've been struggling for a few days on making this work.

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 | ... | Column 15
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:

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?

 

[Rijnsent]

Hi Alec,

I gave your code a try and bumped into the same error when I stopped the code and it tried to create a pivot sheet of a pivot sheet, as it passed by there because of the way you're looping through the sheets. I took the liberty to rewrite your code, and move some bits to the top instead of in your big loop. This code runs smoothly on my pc with my dummy data.

Hope it works for you too,

Koen

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
Dim ArrayShts() As String

'Loop 1: remove unwanted sheets and rename long names
Application.DisplayAlerts = False
For Each Sht In ActiveWorkbook.Worksheets
    If Len(Sht.Name) >= 25 Then Sht.Name = Left(Sht.Name, 25)
    If Sht.Name = "PivotTable" Then Sht.Delete
    If Right(Sht.Name, 6) = " Pivot" Then Sht.Delete
Next
Application.DisplayAlerts = True

'Loop 2: put all sheet names in an array, so no sheets that are inserted distrort the loop later
i = 1
For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Name <> "Programming" Then
        ReDim Preserve ArrayShts(1 To i)
        ArrayShts(i) = Sht.Name
        i = i + 1
    End If
Next

'Loop 3: Loop through the sheets in the array
For i = 1 To UBound(ArrayShts)
    shtName = ArrayShts(i)
    Set datasht = Worksheets(shtName)
    
    'Add sheet for pivot
    Worksheets.Add(before:=datasht).Name = shtName & " Pivot"
    Set pivsht = Worksheets(shtName & " Pivot")
    
    '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, pivrange)
    Set pivtable = pivcache.CreatePivotTable(Tabledestination:=pivsht.Cells(1, 1), TableName:="PivTable")

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

End Sub