Sorry my misktake

I was attemping to display the the pivot items in the column, and hide any
that were not EUR, USD, GBP pivot item values from being displayed using the
visible property.

Thanks for your input. I still cant get it to work.

"Tom Ogilvy" wrote:

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