End if with block if - Pivot Table Problem

[Nav]

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:=xlDatabase, 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("ASSET_PCENT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MARKET_VALUE")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MARKET_VALUE_LOCAL")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL")
If PivotItems = "GBP" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else
If PivotItems = "eur" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else
If PivotItems = "usd" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else

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

Thank you in advance for your help.

Nav

 

[Tom Ogilvy]

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:=xlDatabase, 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("ASSET_PCENT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MARKET_VALUE")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("Dremel").PivotFields("MARKET_VALUE_LOCAL")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
End With '<===== added
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL")
If PivotItems = "GBP" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else
If PivotItems = "eur" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else
If PivotItems = "usd" Then
ActiveSheet.PivotTables("Dremel").PivotFields("SYMBOL").PivotItems.Visible =
True Else

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

 

[Tom Ogilvy]

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.

 

[Nav]

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]

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").PivotItems
pvtItm.Visible = True
Next
for each pvtItm in ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("SYMBOL").PivotItems
if pvtItm <> PivotItems then
pvtItm.Visible = False
end if
Next

 

[Nav]

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.