VBA Pivot table help

[sjd371]

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.

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

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.

 

[pbornemeier]

Couldn't fully test the following code, but I think it will work for you.
Note: Select as little as possible, your code will run quicker.

Sub ProcessMachine(newMachineNumber As String)

    Dim lX As Long
    Dim bFound As Boolean
    
    'On Error GoTo ErrHandler:

    With Sheets("parts_performance").PivotTables(1).PivotFields("mach_name")
        For lX = 1 To .PivotItems.Count
            If .PivotItems(lX).Name = newMachineNumber Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
            'Have to have at least one .PivotItem visible for each .PivotField
            .PivotItems(newMachineNumber).Visible = True
            For lX = 1 To .PivotItems.Count
                If .PivotItems(lX).Name <> newMachineNumber Then
                    .PivotItems(lX).Visible = False
                End If
            Next
        Else
            'Actions to take if a required machine number was not in data
            ReportError newMachineNumber, "parts_performance"
        End If
    End With
    
    bFound = False
    With Sheets("down_time").PivotTables(1).PivotFields("mach_name")
        'What if there was no down time for a machine that had good parts performance
        '  best to check again
        For lX = 1 To .PivotItems.Count
            If .PivotItems(lX).Name = newMachineNumber Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
            'Have to have at least one .PivotItem visible for each .PivotField
            .PivotItems(newMachineNumber).Visible = True
            For lX = 1 To .PivotItems.Count
                If .PivotItems(lX).Name <> newMachineNumber Then
                    .PivotItems(lX).Visible = False
                End If
            Next
        Else
            'Actions to take if a required machine number was not in data
            ReportError newMachineNumber, "down_time"
        End If
    End With
    
End Sub
    
Sub ReportError(newMachineNumber As String, PT_Data As String)
    
    Dim LastRow As Long
    
    With Worksheets("Cell Report")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        With .Cells(LastRow + 1, "A")
            .Value = newMachineNumber
            With .Interior
                .ColorIndex = 3 'Red
                .Pattern = xlSolid
            End With
        End With
    End With

End Sub

 

[sjd371]

Thanks for the response
I tried but it seems to get stuck in a loop. My code will post the (newMachineNumber) to the Sheets("Cell Report") in red as it should if there is no data but I cant close it if there is no data in the table for that machine number. The machine number is in the drop down and I can select it manually with no issues. But when I try to with code I get " unable to set the property of the pivot table class" there must be a simple way to make this work.

 

[pbornemeier]

What line in the code in post #2 hangs?
The first For-Next loop in each section sets bFound to True if the desired machine has data in the PivotTable
The line before the next For-Next loop makes that machine data visible in the PT and that loop hides all other items.

Is the drop down you refer to in "The machine number is in the drop down" a part of the pivot table, or a drop down box on the worksheet (or form) that contains a hardcoded list of valid machines?