Auto Pivot

[Macro_Maniac]

Dear All,
Could anyone suggest how the Auto Filter can be achieved in Pivot (excel 2007) for following scenario thru VBA or other mode?

SCENARIO :

I have 3 sheets in a workbook,

1. SAMPLE DATA sheet:
[TABLE="class: grid, ******* 444"]
<tbody>[TR]
[TD]S.NO
[/TD]
[TD]City
[/TD]
[TD]Country
[/TD]
[TD]Category
[/TD]
[TD]Sub-Cat
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]New York
[/TD]
[TD]US
[/TD]
[TD]Character
[/TD]
[TD]Alpha
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]London
[/TD]
[TD]UK
[/TD]
[TD]Character
[/TD]
[TD]Numeric
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Tokyo
[/TD]
[TD]Japan
[/TD]
[TD]Character
[/TD]
[TD]AlphaNumaric
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]

2. PIVOT sheet For SAMPLE DATA as below:


[TABLE="******* 490"]
<tbody>[TR]
[TD]City
[/TD]
[TD="colspan: 2"](All)
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Category
[/TD]
[TD="colspan: 2"](All)
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sum of Count
[/TD]
[TD="colspan: 2"]Column Labels
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Row Labels
[/TD]
[TD="colspan: 2"]Alpha
[/TD]
[TD="colspan: 2"]AlphaNumaric
[/TD]
[TD="colspan: 2"]Numeric
[/TD]
[TD="colspan: 2"]Grand Total
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Japan
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]12
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]12
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]UK
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]11
[/TD]
[TD="colspan: 2"]11
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]US
[/TD]
[TD="colspan: 2"]10
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]10
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total
[/TD]
[TD="colspan: 2"]10
[/TD]
[TD="colspan: 2"]12
[/TD]
[TD="colspan: 2"]11
[/TD]
[TD="colspan: 2"]33
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

3. Summary Sheet :
[TABLE="class: grid, ******* 345"]
<tbody>[TR]
[TD]Category (ColA)
[/TD]
[TD]Sub-Cat
(ColB)
[/TD]
[TD]Notes
(ColC)
[/TD]
[/TR]
[TR]
[TD]Character
[/TD]
[TD]Alpha
[/TD]
[TD]Only apha info
[/TD]
[/TR]
[TR]
[TD]Character
[/TD]
[TD]Numeric
[/TD]
[TD]Only Numeric info
[/TD]
[/TR]
[TR]
[TD]Character
[/TD]
[TD]AlphaNumaric
[/TD]
[TD]Only Alpha numeric info
[/TD]
[/TR]
</tbody>[/TABLE]


Now, when I click on ALPHA on Summary sheet, below is I want to happen in Pivot table,
a. Report Filter should be as per Category (ColA) i.e. Character.
b. Alpha should be selected in Column Label filter

Expected Output:
[TABLE="******* 308"]
<tbody>[TR]
[TD]
City
[/TD]
[TD="colspan: 2"]
(All)
[/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Category
[/TD]
[TD="colspan: 2"]Character
[/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sum of Count
[/TD]
[TD="colspan: 2"]Column Labels
[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Row Labels
[/TD]
[TD="colspan: 2"]Alpha
[/TD]
[TD="colspan: 2"]Grand Total
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]US
[/TD]
[TD="colspan: 2"]10
[/TD]
[TD="colspan: 2"]10
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total
[/TD]
[TD="colspan: 2"]10
[/TD]
[TD="colspan: 2"]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Any suggestion/Help in this regard will highly appriciate.

Thanks you.

 

[jorismoerings]

Hi,

Check if this is what your after:

1. Create Hyperlinks for the values in Cells B2:B4 each hyperlink must direct to the Cell address; (eg the Hyperlink in B2 must be directed to B2)
2. Make sure the code below is posted on the Codesheet of the Summary page

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

'Set the Variables to be used
Dim pt As PivotTable
Dim Field1, Field2 As PivotField
Dim pivot_item As PivotItem
Dim NewCat, NewSubCat As String

'Here you amend to suit your data
Set pt = Worksheets(2).PivotTables("PivotTable1")
Set Field1 = pt.PivotFields("Category")
Set Field2 = pt.PivotFields("Sub-Cat")

'Here you check which cell is clicked
If Target.Range.Address = "$B$2" Then
        NewCat = Worksheets(3).Range("A2").Value
        NewSubCat = Worksheets(3).Range("B2").Value
    ElseIf Target.Range.Address = "$B$3" Then
        NewCat = Worksheets(3).Range("A3").Value
        NewSubCat = Worksheets(3).Range("B3").Value
    ElseIf Target.Range.Address = "$B$4" Then
        NewCat = Worksheets(3).Range("A4").Value
        NewSubCat = Worksheets(3).Range("B4").Value
    End If

'This updates and refreshes the PIVOT table
With pt
    Field1.ClearAllFilters
    Field1.CurrentPage = NewCat
    Field2.ClearAllFilters
    For Each pivot_item In pt.PivotFields("sub-cat").PivotItems
        If pivot_item.Name <> NewSubCat Then
            pivot_item.Visible = False
        End If
    Next
    pt.RefreshTable
End With

End Sub

 

[Macro_Maniac]

Thanks a top Joris.. This is pretty much i was looking for.
if possible could you please help clarify below,
a. In summary sheet, I have many rows with different cat/sub cat., Is there a easy way to create hyperlink?
b. below section of code only looks till A4/B4 cell, however I have many rows in the summarry sheet as mentioned in above point, So is it possible to loop it instaed?

'Here you check which cell is clicked
If Target.Range.Address = "$B$2" Then
        NewCat = Worksheets(3).Range("A2").Value
        NewSubCat = Worksheets(3).Range("B2").Value
    ElseIf Target.Range.Address = "$B$3" Then
        NewCat = Worksheets(3).Range("A3").Value
        NewSubCat = Worksheets(3).Range("B3").Value
    ElseIf Target.Range.Address = "$B$4" Then
        NewCat = Worksheets(3).Range("A4").Value
        NewSubCat = Worksheets(3).Range("B4").Value
    End If

Thanks,

 

[jorismoerings]

Hmm, don't know about that; it probably will but are you set on using VBA?

Because if you havce many options to choose from; i would suggest puttin' all options possible in a data validation list.
Select a value from that list and have the VBA started from there.

Would make the IF construction i suggested much easier and LOOP through all available options not necessary.

 

[Macro_Maniac]

ok, Thanks