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:

S.NO	City	Country	Category	Sub-Cat	Count
1	New York	US	Character	Alpha	10
2	London	UK	Character	Numeric	11
3	Tokyo	Japan	Character	AlphaNumaric	12

<tbody>
</tbody>

2. PIVOT sheet For SAMPLE DATA as below:

City	(All)
Category	(All)
Sum of Count	Column Labels
Row Labels	Alpha	AlphaNumaric	Numeric	Grand Total
Japan		12		12
UK			11	11
US	10			10
Grand Total	10	12	11	33

<tbody>
</tbody>

3. Summary Sheet :

Category (ColA)	Sub-Cat (ColB)	Notes (ColC)
Character	Alpha	Only apha info
Character	Numeric	Only Numeric info
Character	AlphaNumaric	Only Alpha numeric info

<tbody>
</tbody>

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:

City	(All)
Category	Character
Sum of Count	Column Labels
Row Labels	Alpha	Grand Total
US	10	10
Grand Total	10	10

<tbody>
</tbody>

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