Need to Create Pivot table in Particular Range with VBA

satya12

Board Regular
Joined
Oct 19, 2021
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
We will have data in one sheet. We need to create multiple pivots in another sheet based on particular range like. C2 to H9 need to create one Pivot table. Source file will be suppose "Data " sheet. like that we want to create another pivot in that sheet in range of AA2 to AJ10. I tried one VBA Code its not working. ANd one filed not coming to Columns also.
1657628739058.png

Need Pivot like this. Ticket Status i kept in row field manually. But not coming like this in Col. please help me .
VBA Code:
Sub Pivot()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim filterValue As String
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Pivot Summary"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot SUmm")
Set DSheet = Worksheets("Data")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")





'Insert Row Fields
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("WeekNum")
        .Orientation = xlPageField
        .Position = 6
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("WeekNum")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("WeekNum").LayoutForm _
        = xlTabular
        


With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Ticket Status")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Ticket Status").LayoutForm _
        = xlTabular
        
        
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Resolution SLA Met / Not")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Resolution SLA Met / Not").LayoutForm _
        = xlTabular
        

        
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Resolution SLA Met / Not")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.Name = "SLA Met/Not"
End With



TableActivesheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = _
TrueActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

satya12

Board Regular
Joined
Oct 19, 2021
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
I tried this code but, i am getting error "Invalid Procedure or argument" on last line not able to go for another lines. Please help on this

VBA Code:
Sub PivotTable()

  Dim PTable As PivotTable
  Dim PCache As PivotCache
  Dim PRange As Range
  Dim PSheet As Worksheet
  Dim DSheet As Worksheet
  Dim LR As Long
  Dim LC As Long
  On Error Resume Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet
    Worksheets.Add After:=ActiveSheet ' This will add new worksheet
    ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet"
On Error GoTo 0
Set PSheet = Worksheets("Pivot Sheet")
Set DSheet = Worksheets("Data")
LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = DSheet.Cells(1, 1).Resize(LR, LC)

Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=("PSheet!R3C4:R10C13"), TableName:="Table1", DefaultVersion:=6)

End Sub
 

Forum statistics

Threads
1,175,504
Messages
5,897,821
Members
434,679
Latest member
saede1428

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
Top