Posted to microsoft.public.excel.programming
|
|
Pivot Table Field
Many thanx
"Debra Dalgleish" wrote:
Assuming this is a page field, you could use code similar to the following:
'=========================
Sub PivotOneEmp()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strEmp As String
Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Employee")
strEmp = ActiveSheet.Range("F2").Value
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.CurrentPage = strEmp
For Each pi In pf.PivotItems
If pi.Name = strEmp Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
Application.ScreenUpdating = True
End Sub
'=====================
Naz wrote:
Hi All,
I have a spreadsheet that contains sales by sales rep. Its very simple, I
have the sales rep name and the total sales for the year, which is a simple
sumif from a data list.
But what I want is to create a macro where you select the sales rep ID and
click a button and a pivot table opens in a new workbook showing the sales by
period (months). I'm almost there the only problem I can not figure out is
how to set the pivot table field to show only the information for the
selected sales rep. I have tried recording a macro to see how to do it but
all I get is this
Sub Macro1()
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Employee")
.PivotItems("Ian Smith").Visible = False
.PivotItems("John Smith").Visible = False
.PivotItems("Nat Li").Visible = False
.PivotItems("Vivien Soul").Visible = False
End With
End Sub
With the one I have picked not in the list hence the property would be
visible=true.
Is there any way I can get the macro to say hide all except the chosen one,
as the list of sales reps constanty changes.
All help is greatly appreciated
Naz
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
|