Hi,
I am trying to use VBA to create a pivot table from my data. I used to have this code and it worked perfectly in Excel 2003, but now on Excel 2010 it doesn't work anymore, it shows:
Error 1004 - Unable to set the visible property of the pivot class.
Here is the code:
Thanks for your help
I am trying to use VBA to create a pivot table from my data. I used to have this code and it worked perfectly in Excel 2003, but now on Excel 2010 it doesn't work anymore, it shows:
Error 1004 - Unable to set the visible property of the pivot class.
Here is the code:
Code:
Sub pivot_table()
ActiveSheet.Name = "Transactions"
Sheets("Transactions").Select
Range("A1").Select
'Create PivotTable
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.UsedRange).CreatePivotTable _
TableDestination:="", TableName:="TXM", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("TXM").PivotFields("ACCOUNT").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("TXM").PivotFields("ACCOUNT_NAME").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("TXM").PivotFields("PURP_CD").Caption = _
"Transactions"
ActiveSheet.PivotTables("TXM").AddFields RowFields:=Array("ACCOUNT", _
"ACCOUNT_NAME"), ColumnFields:="Transactions"
With ActiveSheet.PivotTables("TXM").PivotFields("CASH_AMT IN USD")
.Orientation = xlDataField
.Caption = "Sum of CASH_AMT IN USD"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False
'Select which Items will be visible in order to group them by selecting range.
Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables("TXM")
Set PTT = ActiveSheet.PivotTables("TXM").PivotFields("Transactions")
With PTT
.PivotItems("FEES").Visible = True '------>here is the error
For Each Pi In .PivotItems
Select Case Pi.Name
Case "REBATES ", "TRANSFER",
Pi.Visible = True
Case Else
Pi.Visible = False
End Select
Next Pi
End With
Thanks for your help