VBA - Pivot Tables

[MarcusL]

Hi All,

I am new to VBA and fighting with it!

1) Why can't I rename a pivot table. The first line of the below code will work but the second line when I try and experimentally rename it, it won't. What is the reason for this:

Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable1")
Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable50")

2) The second issue I am having is trying to place a filter on pivot table. I can get the line of code to work on all other piovt tables BUT this one. The PivotTable name remains the same so I really don't know why it wont work in this instance. Does anyone know what I am doing wrong. The specific line of code is:

PvtTbl.PivotFields("Netting_Id").PivotFilters.Add Type:=xlValueDoesNotEqual, DataField:=PvtTbl.PivotFields("Sum of CVA_EAD"), Value1:=1

and it is part of this sub:

Sub PivotTB()

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

Set wsData = Worksheets("UnderlyingData")
Set wsPvtTbl = Worksheets("Pivot")
Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable1")

For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
Worksheets("UnderlyingData").Activate
wsData.Range("A3", Range("W3").End(xlDown)).Select
Set rngData = wsData.Range("A3", Range("W3").End(xlDown))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
version:=xlPivotTableVersion11).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion11
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
PvtTbl.ManualUpdate = True
Set pvtFld = PvtTbl.PivotFields("Internal Legal Entity Cis Code")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("Netting_Id")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("CVA_Exemption")
pvtFld.Orientation = xlPageField

With PvtTbl.PivotFields("CVA_EAD")
      .Orientation = xlDataField
      .Function = xlSum
      .NumberFormat = "#,##0"
End With

PvtTbl.PivotFields("Netting_Id").PivotFilters.Add Type:=xlValueDoesNotEqual, DataField:=PvtTbl.PivotFields("Sum of CVA_EAD"), Value1:=1
 
End Sub

Thanks very much for your help!

M