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

radonwilson

New Member
Joined
Jun 23, 2021
Messages
49
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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)"


Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top