Excel VBA Getting Error while creating Pivot Table with Range & Table

[radonwilson]

Code 1 (Source Data as a → Table)
Error :- Run-time error "1004" :- Unable to get the PageFileds property of the PivotTable class.
Code Breaking on Line "With .PageFields("type")"
there are no leading and trailing spaces errors as I have already checked it. Also when my pivot table got created I can see all Pagefields on Pivot Table Excel Sheet.

Option Explicit

Sub cleanup()

Dim tbl As Range
Dim ws As Worksheet

Set ws = ActiveSheet

Rows("1:10").Delete


Range("D:D").Select
Selection.Cut
Range("A:A").Select
Selection.Insert

Range("C:E").Select
Selection.Insert

Range("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth

Range("C:E").Delete

    Cells.Replace "Electronic Transactions (Credit Card/Net Banking/GC)", "ET"
    Cells.Replace "Cash On Delivery Transactions and Non-Transactional Fees", "COD"
  
Set tbl = Range("a1").CurrentRegion
tbl.Select
Selection.WrapText = False


With ws.ListObjects.Add(xlSrcRange, tbl, , xlYes)

.Name = "Trans"
.TableStyle = ""
.HeaderRowRange.Font.Bold = 1
End With

End Sub


Sub Pivot_Table()


Call DeletePT ' // Delete Existing Sheet Containing Pivot Table

Worksheets.Add after:=Trans '// Inserted New Sheet After the Transaction Sheet For Pivot Table
Worksheets(2).Name = "Pivot"
Dim MyTable As PivotTable
Dim MyCache As PivotCache


Set MyCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Trans.ListObjects("Trans"))  ' // trans is the sheet code name of my source data sheet
Set MyTable = MyCache.CreatePivotTable(Worksheets("Pivot").Range("A3"), "Pivot Table")


'// My Table Layout Settings

With MyTable
    .RowAxisLayout xlTabularRow
    .ColumnGrand = False
    .RowGrand = False
    .TableStyle2 = "PivotStyleTable2"
    .HasAutoFormat = False
  
  
    '// Filters
    With .PageFields("type")
    .Orientation = xlPageField
    .Position = 1
    End With
  
    '// Rows Section
    With .PageFields("order id")
          .Orientation = xlRowField
          .Position = 1
    End With
 


End With







'   ///  Release Object Memory
Set MyTable = Nothing
Set MyCache = Nothing

End Sub


Sub DeletePT()

On Error Resume Next

Application.DisplayAlerts = False

ThisWorkbook.Worksheets("Pivot").Delete

Application.DisplayAlerts = True


End Sub

Code 2 (Source Data as a → Range)
Error :- Run-time error "424" :- Object Required
Code Breaking on Line "Set MyCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, MyRange)"

Sub Pivot_Table()

'// Variables
Dim LastRow As Long
Dim LastCol As Long
Dim MyRange As Range

LastRow = Trans.Cells(Rows.Count, 1).End(xlUp).Row 'trans is my data sorce sheet code name
LastCol = Trans.Cells(1, Columns.Count).End(xlToLeft).Column

Set MyRange = Range(Cells(1, 1), Cells(LastRow, LastCol))

Dim MyTable As PivotTable
Dim MyCache As PivotCache

Call DeletePT ' // Delete Existing Sheet Containing Pivot Table

Worksheets.Add after:=Trans '// Inserted New Sheet After the Transaction Sheet For Pivot Table
Worksheets(2).Name = "Pivot"

Set MyCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, MyRange)
Set MyTable = MyCache.CreatePivotTable(Worksheets("Pivot").Range("A1"), "Pivot Table")

With MyTable

'// My Table Layout Settings
    .RowAxisLayout xlTabularRow
    .ColumnGrand = False
    .RowGrand = False
    .TableStyle2 = "PivotStyleLight2"
    .HasAutoFormat = False
  
  
    '// Filters
    With .PageFields("type")
    .Orientation = xlPageField
    .Position = 1
    End With
  
    '// Rows Section
    With .PageFields("order id")
          .Orientation = xlRowField
          .Position = 1
    End With
 


End With

'   ///  Release Object Memory
Set MyTable = Nothing
Set MyCache = Nothing
Set MyRange = Nothing

End Sub
Sub DeletePT()

On Error Resume Next

Application.DisplayAlerts = False

ThisWorkbook.Worksheets("Pivot").Delete

Application.DisplayAlerts = True


End Sub