Create another Pivot table in next +1 column

[Sanoj]

Dear all,

I have prepared a macro based Pivot table and it may grow to D, E, F etc. its not of a specific length. and I am trying to create another Pivot table next to it using macro but not sure how to put it 1+ next column. That is if the 1st pivot ends at E next pivot should begin from G.
Following is the code which currently I am using.

Sub Main_Macro()

'Declare Variables for Pivot table
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRowp As Long
Dim LastCol As Long
'InsertPivotTable()

'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Report")


'Define Data Range
LastRowp = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRowp, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="ADT_PivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="ADT_PivotTable")

'Insert Reportfilter Fields
  With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Resp Bus Partn ID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("ADT-File ID")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("UWY")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("SCoB - Acc")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Curr")
        .Orientation = xlPageField
        .Position = 1
    End With

'Insert Row Fields
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Bus Ttl")
        .Orientation = xlRowField
        .Position = 1
'End With
'With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Month")
'        .Orientation = xlRowField
'        .Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("EC")
        .Orientation = xlColumnField
        .Position = 1
        .NumberFormat = "#,##0.00"
End With
'Insert Data Field
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Book Amt in Orig Curr")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        '.Name = "Revenue "
End With
'Format Pivot Table
'ActiveSheet.PivotTables("ADT_PivotTable").ShowTableStyleRowStripes = True
'ActiveSheet.PivotTables("ADT_PivotTable").TableStyle2 = "PivotStyleMedium9"


'Declare Variables
Dim PSheet1 As Worksheet
Dim DSheet1 As Worksheet
Dim PCache1 As PivotCache
Dim PTable1 As PivotTable
Dim PRange1 As Range
Dim LastRow2 As Long
Dim LastCol2 As Long

Application.DisplayAlerts = True

Set DSheet1 = Worksheets("Report")
'Define Data Range
With DSheet1
    LastRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol2 = Sheets("Pivot").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Set PRange1 = .Range("A1").Resize(LastRow2, LastCol2) ' set data range for Pivot Table
End With

' Set the Pivot Cache
Set PCache1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange1)

On Error Resume Next
Set PTable1 = PSheet1.PivotTables("ADT_PivotTable2") ' Set the Pivot Table if already exists from previous code runs

On Error GoTo 0
If PTable1 Is Nothing Then ' <-- Pivot Table still doesn't exist >> create it for the first time
    ' create a new Pivot Table in "Pivot" sheet
    Set PTable1 = PSheet1.PivotTables.Add(PivotCache:=PCache1, TableDestination:=PSheet1.Range("LastCol2"), TableName:="ADT_PivotTable2")

    With PTable1
      'Insert Row Fields
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Entry Code")
        .Orientation = xlRowField
        .Position = 1
End With

'Insert Column Fields
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Policy Holder")
        .Orientation = xlColumnField
        .Position = 1
        .NumberFormat = "#,##0.00"
    End With
'Insert Data Field
With ActiveSheet.PivotTables("ADT_PivotTable").PivotFields("Amount")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0.00"
    End With
    End With
Else
    ' just refresh the Pivot cache with the updated Range
    PTable1.ChangePivotCache PCache1
    PTable1.RefreshTable
End If

End Sub

 

[Sanoj]

Any clue...