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






--

_______________________
Naz,
London

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

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