I have a pivot table i need to index 1 set of records at a time. The problem is that sometimes not all of the machines have reported back information so trying to open that one returns an error " unable to set the property of the pivot table class".
To handle some of this I have some code that will return the machine number back to the report and highlight the row color red. then continue running down the rest of the machine numbers. The problem is I need to close the last machine before continuing other wise it will stay open and keep showing up on the report.
So in this example if machine 311023 did not return any data it would show up red on the report then machine 311024 would open BUT machine 311030 would not close and show up on the report after every row of code. There must be a simple way to ignore this error or go back to close it that I have not found yet.
To handle some of this I have some code that will return the machine number back to the report and highlight the row color red. then continue running down the rest of the machine numbers. The problem is I need to close the last machine before continuing other wise it will stay open and keep showing up on the report.
Code:
Sub Set_Cell_1()
Debug.Print "Enter Set Cell 1..."
Sheets("Cell Report").Select
Range("A3") = "CELL 1"
Range("B3") = Sheets("Report_Date").Cells(1, 4)
Call ProcessMachine("310030", "308026")
Call ProcessMachine("311023", "310030")
Call ProcessMachine("311024", "311023")
Call ProcessMachine("311027", "311024")
End Sub
Code:
Sub ProcessMachine(newMachineNumber As String, oldMachineNumber As String) On Error GoTo ErrHandler:
Sheets("parts_performance").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("mach_name")
.PivotItems(newMachineNumber).Visible = True
.PivotItems(oldMachineNumber).Visible = False
End With
Sheets("down_time").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("mach_name")
.PivotItems(newMachineNumber).Visible = True
.PivotItems(oldMachineNumber).Visible = False
End With
' Application.Run ("send")
Exit Sub
ErrHandler:
'error handling code
Sheets("Cell Report").Select
Dim LastRow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Cells(LastRow + 1, "A").Select
Cells(LastRow + 1, "A").Value = newMachineNumber
ActiveSheet.Rows(LastRow + 1).Select
With Selection.Interior
.ColorIndex = 3 'Red
.Pattern = xlSolid
End With
Exit Sub
End Sub
So in this example if machine 311023 did not return any data it would show up red on the report then machine 311024 would open BUT machine 311030 would not close and show up on the report after every row of code. There must be a simple way to ignore this error or go back to close it that I have not found yet.