Hi,
I have used this script in the past with several worksheets and had no problems. I recently made some major changes to the worksheet I am now trying to run this on and now I have problems.
I first thought that all the code in the new sheet was causing a conflict so I thought I would try to remove all the code then try this script. However no luck...
I keep getting an error when trying to create the pivot table
Run Time Error '1004'
The PivotTable field name is not valid.
To create a PivotTable report,you must
use data that is organized as a list with
labeled columns. if you are changing the
name of a PivotTable field, you much type
a new name for the field.
I have attached the sheet I am running this script on to help whomever can help figure out what is wrong.
Thank You, Mike
Sub LANEW() 'New Production Sheets
'On Error Resume Next
'Call RemoveAllMacros
'Call RemoveCodeLANEW
ActiveSheet.unProtect "tomcat"
ActiveSheet.Shapes("CommandButton3").Delete
ActiveSheet.Shapes("CommandButton4").Delete
ActiveSheet.Shapes("CommandButton5").Delete
Rows("1:3").Delete Shift:=xlUp
Columns("B:B").Delete Shift:=xlToLeft
Columns("D:D").Delete Shift:=xlToLeft
Columns("G:I").Delete Shift:=xlToLeft
Application.ScreenUpdating = False
[A:A].AutoFilter Field:=1, Criteria1:="="
[A2:A5000].SpecialCells(xlVisible).EntireRow.Delete
If [A1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[B:B].AutoFilter Field:=1, Criteria1:="="
[B2:B5000].SpecialCells(xlVisible).EntireRow.Delete
If [B1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Columns("A:A").Select
Selection.CheckSpelling SpellLang:=1033
Range("b1").Value = "Order #"
Range("a1").Value = "Name"
Call PivotPROLANEW
End Sub
Sub PivotPROLANEW()
Dim ws As Worksheet, strSource As String
Dim PT As PivotTable, pc As PivotCache, pf As PivotField
For Each ws In ActiveWorkbook.Worksheets
ActiveSheet.unProtect "tomcat"
'ActiveWorkbook.ShowPivotTableFieldList = False
' Application.CommandBars("PivotTable").Visible = False
'Application.DisplayAlerts = False
'Application.ScreenUpdating = False
strSource = ws.Name & "!R1C1:R2000C8"
ActiveWorkbook.Worksheets.Add 'new worksheet becomes the active sheet
ActiveSheet.Name = "Info"
Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, strSource)
Set PT = pc.CreatePivotTable(Range("A1")) 'pivot table at A1 on new sheet
Set pf = PT.PivotFields("Order #")
PT.AddDataField pf, "Count of Order Number", xlCount
PT.AddFields RowFields:="Name"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Next ws
DATA = "Data"
Worksheets.Add().Name = DATA
Sheets("Info").Select
Bookmarks