Error Message - .PivotItems("(blank)").Visible = True

**srinuy2kin** · 11-24-2011, 05:57 AM

Hi Team,

I have recorded a macro its working fine with few reports & not with few reports , I checked with the old post too but it’s not working the error message is - .PivotItems("(blank)").Visible = True (Runtime Error – 1004).Can anyone help on this:

The whole macro looks like this :
Application.Goto Reference:="R1C1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"INTERFACE_ERROR_REP_NL!R1C1:R63C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MESSAGE_TEXT")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT_IN_USD")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("AMOUNT_IN_USD"), "Count of AMOUNT_IN_USD", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of AMOUNT_IN_USD")
.Caption = "Sum of AMOUNT_IN_USD"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MESSAGE_TEXT")
.PivotItems("(blank)").Visible = True
On Error GoTo 0
End With
Columns("B:B").Select
Selection.Style = "Comma"
ActiveSheet.PivotTables("PivotTable1").PivotSelect "MESSAGE_TEXT[All]", _
xlLabelOnly + xlFirstRow, True
Range("A9").Select
ActiveWindow.Zoom = 75

'
' Msg Macro
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Message Text"
Range("D20").Select

Application.Goto Reference:="INTERFACE_ERROR_REP_NL!R[1]C[10]"
Application.Goto Reference:="R1C1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"INTERFACE_ERROR_REP_NL!R1C1:R63C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CREATION_DATE ")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("AMOUNT_IN_USD")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("AMOUNT_IN_USD"), "Count of AMOUNT_IN_USD", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CREATION_DATE ")
.PivotItems("(blank)").Visible = False
End With
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of AMOUNT_IN_USD")
.Caption = "Sum of AMOUNT_IN_USD"
.Function = xlSum
End With
Range("C14").Select
ActiveWindow.Zoom = 75

'
' Dat Macro
'

'
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Date"
Range("D20").Select
Application.Goto Reference:="INTERFACE_ERROR_REP_NL!R[-4]C[8]"
Application.Goto Reference:="R1C16"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$63").AutoFilter Field:=16, Criteria1:= _
"You must supply an accounting rule duration when you supply an accounting rule of type Variable Duration."
Columns("M:P").Select
Selection.EntireColumn.Hidden = True
Range("L66").Select
Columns("L:L").ColumnWidth = 22.14
Columns("L:L").ColumnWidth = 25.29
Range("Q1").Select
ActiveCell.FormulaR1C1 = "START DATE"
Range("R1").Select
ActiveCell.FormulaR1C1 = "END DATE"
Range("S1").Select
ActiveCell.FormulaR1C1 = "COMMENTS"
Range("Q61").Select
Columns("Q:Q").ColumnWidth = 12.57
Columns("R:R").ColumnWidth = 11.71
Columns("S:S").ColumnWidth = 12.71
Rows("1:1").Select
Range("S1").Activate
Selection.Font.Bold = True
Range("Q66").Select
ActiveWindow.Zoom = 75
Range("L73").Select

'
' Name Macro
'

'
Sheets("INTERFACE_ERROR_REP_NL").Select
Sheets("INTERFACE_ERROR_REP_NL").Name = "Interface Error Report NL"
Range("G16").Select
End Sub

**arlu1201** · 11-24-2011, 06:55 AM

First of all, please put the code within code tags.

2nd, It will be good if you attach the workbook that you are using this code with. This will make it easier for us to troubleshoot.

**srinuy2kin** · 11-24-2011, 07:59 AM

Originally Posted by srinuy2kin

Hi Team,

I have recorded a macro its working fine with few reports & not with few reports , I checked with the old post too but it’s not working the error message is - .PivotItems("(blank)").Visible = True (Runtime Error – 1004).Can anyone help on this:

The whole macro looks like this :
Application.Goto Reference:="R1C1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"INTERFACE_ERROR_REP_NL!R1C1:R63C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MESSAGE_TEXT")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT_IN_USD")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("AMOUNT_IN_USD"), "Count of AMOUNT_IN_USD", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of AMOUNT_IN_USD")
.Caption = "Sum of AMOUNT_IN_USD"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MESSAGE_TEXT")
.PivotItems("(blank)").Visible = True
On Error GoTo 0
End With
Columns("B:B").Select
Selection.Style = "Comma"
ActiveSheet.PivotTables("PivotTable1").PivotSelect "MESSAGE_TEXT[All]", _
xlLabelOnly + xlFirstRow, True
Range("A9").Select
ActiveWindow.Zoom = 75

'
' Msg Macro
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Message Text"
Range("D20").Select

Application.Goto Reference:="INTERFACE_ERROR_REP_NL!R[1]C[10]"
Application.Goto Reference:="R1C1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"INTERFACE_ERROR_REP_NL!R1C1:R63C16", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CREATION_DATE ")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("AMOUNT_IN_USD")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("AMOUNT_IN_USD"), "Count of AMOUNT_IN_USD", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CREATION_DATE ")
.PivotItems("(blank)").Visible = False
End With
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of AMOUNT_IN_USD")
.Caption = "Sum of AMOUNT_IN_USD"
.Function = xlSum
End With
Range("C14").Select
ActiveWindow.Zoom = 75

'
' Dat Macro
'

'
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Date"
Range("D20").Select
Application.Goto Reference:="INTERFACE_ERROR_REP_NL!R[-4]C[8]"
Application.Goto Reference:="R1C16"
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$63").AutoFilter Field:=16, Criteria1:= _
"You must supply an accounting rule duration when you supply an accounting rule of type Variable Duration."
Columns("M:P").Select
Selection.EntireColumn.Hidden = True
Range("L66").Select
Columns("L:L").ColumnWidth = 22.14
Columns("L:L").ColumnWidth = 25.29
Range("Q1").Select
ActiveCell.FormulaR1C1 = "START DATE"
Range("R1").Select
ActiveCell.FormulaR1C1 = "END DATE"
Range("S1").Select
ActiveCell.FormulaR1C1 = "COMMENTS"
Range("Q61").Select
Columns("Q:Q").ColumnWidth = 12.57
Columns("R:R").ColumnWidth = 11.71
Columns("S:S").ColumnWidth = 12.71
Rows("1:1").Select
Range("S1").Activate
Selection.Font.Bold = True
Range("Q66").Select
ActiveWindow.Zoom = 75
Range("L73").Select

'
' Name Macro
'

'
Sheets("INTERFACE_ERROR_REP_NL").Select
Sheets("INTERFACE_ERROR_REP_NL").Name = "Interface Error Report NL"
Range("G16").Select
End Sub

Originally Posted by arlu1201

First of all, please put the code within code tags.

2nd, It will be good if you attach the workbook that you are using this code with. This will make it easier for us to troubleshoot.

Hi

Please find the attachment, hope it will help

Thanks
Srinivasa

**srinuy2kin** · 11-25-2011, 11:51 AM

Hi, Can anyone help me in this issue...

**arlu1201** · 11-28-2011, 02:48 PM

I checked your code and i found the line

Please Login or Register  to view this content.

Since this is not a valid code line, i changed it. Now it gives me a different error, as its not finding (blank). So let me research a lil into this and find out how we can get rid of that error.

Also, i observed that you are adding one button on top of each other on the main page and each click adds a new button. Is there a specific reason why you are doing that?