PDA

View Full Version : Vba code for 3 pivots in one sheet



Dine
12-26-2015, 12:54 AM
Hi Friends,

I have a data set where I need to have 3 pivots in the same sheet.

Ideally,

*Report Filter headers will be tracking STATUS, DESCRIPTION AND FIELD TRAILS.

Conditions:
STATUS should be "Assigned",Closed ,In progress" Resolved".
DESCRIPTION : Should include all barring words containing "I&IT","Device monitoring",IIT.
FIELD TRAILS : Should have only #NA


*Column Label Filter header will be tracking "PRIORITY"
Conditions: " NO Conditions"

* Row Label Filter header will be tracking "Reported Date"

Condition:

Reported Date should be grouped in Years and the current month Date should be greater than >9/1/2015 , assuming september 2015 is the current month.

Clue : MonthEndDate


**Values Filter header will be tracking "Count of Incidents."


Any help on this code will be very helpful.

Attaching sample file for your reference with all the 3 pivots defining the above criteria.

Thanks Again.

Dine
12-26-2015, 11:22 AM
Hi Friends,

While setting up the vba code for the above, I am not able to move forward for putting out the below condition in the existing code.

Attached the latest sheet for your reference





Sub SetupPivot()

Application.ScreenUpdating = False

Dim pt As PivotTable
Dim WSD As Worksheet
Set WSD = Worksheets("Base Sheet")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("im volume summary")
Dim PTCache As PivotCache
Dim PRange As Range

'Clean up any pivot tables on the PIVOT page
For Each pt In PTOutput.PivotTables
pt.TableRange2.Clear
Next pt

' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="im volume summary")

' Define the layout of the pivot table

' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True

With pt
.PivotFields("Priority").Orientation = xlColumnField
.PivotFields("Incident ID").Orientation = xlDataField
.PivotFields("Status").Orientation = xlPageField
.PivotFields("Description").Orientation = xlPageField
.PivotFields("Field Trials?").Orientation = xlPageField
.PivotFields("Reported Date").Orientation = xlRowField
End With

Application.ScreenUpdating = True
End Sub

Dine
12-26-2015, 10:49 PM
Hi Friends ,

Managed to write the code for single pivot ...

The challenge being I need two more pivots just besides it.

Just like the below screen.Any suggestions will be appreciated.

Uploaded file for your reference.


15051