[RESOLVED] Change Pivot Table & Pivot Chart through VBA
Dear All,
I have attached a sample file for your reference. In that file, I have three worksheets 1) Data 2) Chart and 3) Pivot.
I have written a small code in "Chart" worksheet to change the Pivot chart based on selection made in the cell "F6".
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Sheets("Chart").Range("F6")) Is Nothing Then
Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Name"). _
ClearAllFilters
Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Name").CurrentPage _
= Sheets("Chart").Range("F6").Value
End If
End Sub
Now Pivot Chart also shows for all months from January to December. But I want to restrict the Pivot Chart even for months. So if I select January, then Pivot Chart should show only January month data and If I select Jan, Feb, Mar, then Pivot Chart should show only Jan, Feb and Mar months data instead of all months.
Current Challenges:
1) I am not able to select multiple months at a time in cell M6, since I have used data validation. Is it possible to use some other option which will help us to select multiple items from the dropdown list?
2) Is it possible to write a VBA code to change the Pivot Chart based on the selection made in Cell M6 along with F6?
Can someone please help me to overcome the above mentioned challenges and achieve required results?