I am still getting an error but it really doesn't like that change! I have tried changing the table name a few times and this code just doesn't want to work
Error is The PivotTable field name is not valid
Here is the full code, I have made some changes which was from Microsoft...still doesn't work.
Sub Create_Pivot()
'
' Create_Pivot Macro
'
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim mySourceData As String
Dim myDestinationRange As String
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable
Dim nm As String
With ThisWorkbook
Set mySourceWorksheet = .Worksheets("Sum_Data")
Set myDestinationWorksheet = .Worksheets("Pivot")
End With
myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
myFirstRow = 1
myFirstColumn = 1
With mySourceWorksheet.Cells
myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
End With
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="NewPivotTable")
With myPivotTable
With .PivotFields("Class").Orientation = xlRowField
With .PivotFields("Jan-18")
.Orientation = xlDataField
.Position = 1
.Functiom = xlSum
.NumberFormat = "#,##0.00"
End With
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End With
End Sub
