Tuesday, 22 March 2016

How to Know the Pivot Field Orientation in Excel Pivot Table with VBA

Excel VBA to get the Pivot Field Orientation in a Pivot Table
Sub Pvt_Fields_Orientation()

Dim Pvt As PivotTable
Dim Pvt_Field As PivotField

Set Pvt = ActiveSheet.PivotTables("MyPivot")

For Each Pvt_Field In Pvt.PivotFields

MsgBox Pvt_Field.Name
MsgBox Pvt_Field.Orientation
'0-xlDataField (Values Section)
'1-xlRowField (Row Section)
'2-xlColumnField (Column Section)
'3-xlPageField (Filter Section )

Next Pvt_Field

End Sub

Example :

Sunday, 20 March 2016

How to disable Right Click Option in the Excel with VBA

Workbook and Worksheet Events to Disable the  Right Click Option in the Excel

WorkSheet Event :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True

 MsgBox ("Sorry the Right Click is Disabled in this WorkSheet")
End Sub

WorkBook Event :

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Cancel = True

 MsgBox ("Sorry The Right Click is Disabled for this Workbook")
End Sub

Saturday, 19 March 2016

How to Apply Pivot Filter from a Range of Values in Excel

How to Pass a Range of Values to Pivot Filter in Excel
Sub Apply_Pivot_Filters_from_Range()
Dim Pvt As PivotTable
Dim Pvt_Item As PivotItem
Dim Input_Rng As Range
Dim Rng As Range
Dim Pvt_Sht As Worksheet

Dim blnTrueFalse As Boolean
blnTrueFalse = False

Set Pvt_Sht = ThisWorkbook.Sheets("Pivots_Tab")

Set Pvt = Pvt_Sht.PivotTables("PivotTable1")

'Finding the Input Range with Values to Pass into the Pivot Filter
Set Input_Rng = Pvt_Sht.Range("A2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, 1)

        Pvt.PivotFields("Sales_Period").CurrentPage = "(All)"
        Pvt.PivotFields("Sales_Period").EnableMultiplePageItems = True


'Applying/Passing each Range value to Pivot Filter
For Each Rng In Input_Rng

 For Each Pvt_Item In Pvt.PivotFields("Sales_Period").PivotItems
'All PivotItems which Matches to the Rng value will be Selected for Filter. 
     If Rng = Pvt_Item Then
         Pvt_Item.Visible = True
'All PivotItems which does not Matches to the Rng value will be De-selected from Filter.
     ElseIf Rng <> Pvt_Item And blnTrueFalse = False Then
        Pvt_Item.Visible = False
     End If

 Next Pvt_Item
 'Setting the Boolean value to 'True', so that in the next loop the Pivot will apply Filter only to the Matching values and does not cares about the Non-Matching UnFIltered values.
 blnTrueFalse = True

Next Rng 

MsgBox "All Input Range Values Applied to Pivot Filter", vbOKCancel, "Apply Filter"
End Sub
Example :
Suppose we have a Pivot Table and the Range of Values to Pass into Pivot Filter as follows :
 Pivot Table :

Range of Values to Pass into Pivot Filter "Sales_Period"

Output :


Tuesday, 8 March 2016

How to Refresh a Pivot Cache and Clear Missing Pivot Items in VBA

Excel Macro to Refresh a Pivot Cache and Clear Missing PivotItems
Sub PvtCache_Clear()

Dim My_Pvt As PivotTable

Dim J as Integer

Set My_Pvt = ThisWorkbook.Sheets("My_Tab").PivotTables("MyPivot")

Clearing Missing Items from Pivot Fields and Refreshing Pivot Cache

My_Pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone

'My_Pvt.ManualUpdate = False

'Refreshing the Pivot Table

X = My_Pvt.PivotFields("MyField").PivotItems.Count

MsgBox X

'Displaying Pivot Items after Pivot Refresh

For Y = 1 To X
   MsgBox My_Pvt.PivotFields("MyField").PivotItems(Y)
Next Y

'De-Selecting the (blanks) from the Pivot
J = My_Pvt.PivotFields("MyField").PivotItems.Count

If J > 2 Then
    With My_Pvt.PivotFields("MyField")
        .PivotItems("(blank)").Visible = False
    End With

Else: End If

End Sub

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts