Need to Create Pivot table in Particular Range with VBA

[satya12]

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.

Need Pivot like this. Ticket Status i kept in row field manually. But not coming like this in Col. please help me .

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

 

[satya12]

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

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