Automating Pivot Table using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub pivotvba()
 With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
For Each wks In Application.Worksheets
        If wks.Name = "Pivot_Table" Then wks.Delete
    Next

 cntsheets = Application.Sheets.Count
    Set NewSheet = Application.Worksheets.Add(After:=Worksheets(cntsheets))
    NewSheet.Name = "Pivot_Table"
   
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
 Application.DisplayAlerts = True
  Dim pt As PivotTable
  Dim pc As PivotCache
  Dim pf As PivotField
  Dim pi As PivotItem

'set the pivotcache
Sheets("Data").Select
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Data").Range("A3").CurrentRegion)

'create the pivot table
Sheets("Pivot_Table").Select
Set pt = ActiveSheet.PivotTables.Add(pc, Range("A3"), "Pivot_Table_1")

'put the fields

With pt
'.PivotFields("Salesperson").Orientation = xlColumnField
.PivotFields("Category").Orientation = xlRowField
.PivotFields("Salesperson").Orientation = xlColumnField
.PivotFields("Revenue").Orientation = xlDataField
'set the number format

.DataBodyRange.NumberFormat = "$#,##0.00"

'classic view

.InGridDropZones = True


'switch back from classic view
.InGridDropZones = False

'add calculated field

.CalculatedFields.Add "Eligible for bonus", "= IF(Revenue >1500,1,0)", True
.PivotFields("Eligible for bonus").Orientation = xlDataField
'Changing the caption of the calculated field,removing the sum of part
.DataPivotField.PivotItems("Sum of Eligible for bonus").Caption = "Eligible for bonus ? "

'changing the number format of the callculated field so that it becomes only 1 and 0

.PivotFields("Eligible for bonus ? ").NumberFormat = "#,##0"
'converting 1 and 0 to yes and no

.PivotFields("Eligible for bonus ? ").NumberFormat = """Yes"";;""No"""

'Add region column as report filter
.PivotFields("Region").Orientation = xlPageField


End With
'Setting default filter

Set pf = pt.PivotFields("Region")
With pf
   For Each pi In pf.PivotItems
   If pi.Name = "East" Then
   pi.Visible = True
   Else
   pi.Visible = False
   End If
  
   Next pi
 End With

'setting filter in a more customized way,suppose
'we want to see revenue generated by eastern region with
'the category beverages,we already filtered the data by east
'now let us filter the filtered table by a cirtain category

Set pf = pt.PivotFields("Category")
With pf
   For Each pi In pf.PivotItems
   If pi.Name = "Beverages" Then
   pi.Visible = True
   Else
   pi.Visible = False
   End If
  
   Next pi
 End With

'Suppose we want to filter column by both east and west

Set pf = pt.PivotFields("Region")
With pf
   For Each pi In pf.PivotItems
   If pi.Name = "East" Or pi.Name = "West" Then
   pi.Visible = True
   Else
   pi.Visible = False
   End If
  
   Next pi
 End With
'Suppose we want to filter the row by both beverages and candy
Set pf = pt.PivotFields("Category")
With pf
   For Each pi In pf.PivotItems
   If pi.Name = "Beverages" Or pi.Name = "Candy" Then
   pi.Visible = True
   Else
   pi.Visible = False
   End If
  
   Next pi
 End With

'Update the pivot table

ThisWorkbook.RefreshAll
End Sub