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:
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:
and it is part of this sub:
Thanks very much for your help!
M
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:
HTML:
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:
HTML:
PvtTbl.PivotFields("Netting_Id").PivotFilters.Add Type:=xlValueDoesNotEqual, DataField:=PvtTbl.PivotFields("Sum of CVA_EAD"), Value1:=1
and it is part of this sub:
HTML:
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