But it appears that these are not columns, but pivotitem values in a single
column. You would control their inclusion by setting the individual
pivotitem's visibility property.


I am surprised you don't get an error on this line:

PivotItems =
ActiveSheet.PivotTables("PivotTable3").PivotFields ("SYMBOL").PivotItems

Anyway, if the string variable PivotItems held the value of one of the
PivotItems in the Pivot Field "symbol", you could do

for each pvtItm in ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("SYMBOL").PivotItem s
pvtItm.Visible = True
Next
for each pvtItm in ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("SYMBOL").PivotItem s
if pvtItm < PivotItems then
pvtItm.Visible = False
end if
Next

--
Regards,
Tom Ogilvy

"Nav" wrote:

I was just attemping to show the GBP, EUR, and USD columns only on the
pivottable. I thought it was possible to do. Maybe I was wrong then.

Thanks.

"Tom Ogilvy" wrote:

Some added

Also - the IF then construct could be problematic - or it just may be caused
by wordwrap in your posting - I can't tell, but this should be as I show it:

If PivotItems = "GBP" Then
ActiveSheet.PivotTables("Dremel").PivotFields( _
"SYMBOL").PivotItems.Visible = True
ElseIf PivotItems = "eur" Then
ActiveSheet.PivotTables("Dremel").PivotFields( _
"SYMBOL").PivotItems.Visible =True
ElseIf PivotItems = "usd" Then
ActiveSheet.PivotTables("Dremel").PivotFields( _
"SYMBOL").PivotItems.Visible = True
Else
ActiveSheet.PivotTables("Dremel").PivotFields( _
"SYMBOL").PivotItems.Visible = False
End If

also the With / End With structure around this statement plays no role, so
it isn't needed.

all that said, however, the PivotItems object doesn't have a visible
property to the best of my knowledge - so I am not sure what you are trying
to achieve with these statements.

--
Regards,
Tom Ogilvy

"Nav" wrote:

Hello

Data is laid out in 5 columns, the first col should populate the row fields,
the second to populate the column fields and the next three should be the sum
data in the pivot table. However I am trying to get the data displayed if
the second column symbol = EUR, or USD. I have tried the recorder and made
some amendments, however this comes up with a €œEnd if with Block If€� compile
error, however I am not able to find where it has gone wrong. The code is
below, any ideas would be appreciated.

Sub Dr

Dim PivotItems As String
PivotItems =
ActiveSheet.PivotTables("PivotTable3").PivotFields ("SYMBOL").PivotItems

Columns("A:E").Select
Range("E1").Activate
Columns("A:E").EntireColumn.AutoFit
Range("B3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!A:E").CreatePivotTable TableDestination:="", TableName:= _
"Dremel", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Dremel").AddFields RowFields:=Array("CLIENT_ID" _
, "Data"), ColumnFields:="SYMBOL"
With ActiveSheet.PivotTables("Dremel").PivotFields("ASS ET_PCENT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MAR KET_VALUE")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MAR KET_VALUE_LOCAL")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Dremel").PivotFields("SYM BOL")
If PivotItems = "GBP" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYM BOL").PivotItems.Visible =
True Else
If PivotItems = "eur" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYM BOL").PivotItems.Visible =
True Else
If PivotItems = "usd" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYM BOL").PivotItems.Visible =
True Else

ActiveSheet.PivotTables("Dremel").PivotFields("SYM BOL").PivotItems.Visible =
False
End If
` error occurs here
End With
ActiveSheet.PivotTables("Dremel").Activate
Range("A4").Select
ActiveSheet.PivotTables("Dremel").PivotFields("CLI ENT_ID").AutoSort _
xlAscending, "Sum of ASSET_PCENT"
End Sub

Thank you in advance for your help.

Nav