Macro that choose a pivotfieldvalue from cell value and updates pivot

[blaksnm]

Hey guys
I have a report-workbook with periodical (monthly) updates/revisions.
The datas is placed as in a "database" (Mainbase) which the pivot-tables (used in my report-sheet) is based on.
By some inputsheets my macro generate this database that expands when a new revision is approved.

My macro designs a report that contains of a number of different pivot-tables - all with use of different dispositions of the available data in Mainbase.

After accumulating the mainbase with all revisions I would like to instruct my macro to show the pivot-tables with just the specified revision (stated in range A1.value)

Will anyone kindly - as many times before - guide me on this one?

 

[Andrew Poulsom]

Have you tried recording a macro while doing it manually? What code do you get?

 

[blaksnm]

Thanks for your interest Andrew
My recorded macro:

Sub Makro1()
With ActiveSheet.PivotTables("Pivottabell1").PivotFields("Rev Current")
.PivotItems("Rev 00").Visible = False
.PivotItems("Rev 01").Visible = False
.PivotItems("Rev 02").Visible = True
End With
End Sub

The expession RevXX is a range ("A1").value in fact
I want only this pivotfieldvalue to show in the pivottable -all other fieldvalues = visible= false

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim PI As PivotItem
    With ActiveSheet.PivotTables("Pivottabell1").PivotFields("Rev Current")
        .PivotItems(Range("A1").Value).Visible = True
        For Each PI In .PivotItems
            PI.Visible = PI.Name = Range("A1").Value
        Next PI
    End With
End Sub

 

[blaksnm]

Thanks a lot Andrew - this is just perfect