VBA Help: Finding Max of a range and pasting in another sheet

[amaluna]

Hello,
I'm trying to compare 5 items in a pivot and then finding the max value. Whichever is the max value will filter the next Pivot. As of right now I have a super long code that works but I was wondering if someone could help me condense it a little. There are 2 pivots exactly the same; one shows the difference in percentage whereas the other shows the numbers. I'm trying to compare the percentage pivot, finding the max there and then copying the actual numbers from the other Pivot and pasting it in another sheet. (So for example, if Apple has the biggest percentage, then you would copy the numbers from the Apple row and pasting it in a different sheet).

Thank you in advance!

For Each ws In wb.Sheets    For Each PT In ws.PivotTables
        PT.ManualUpdate = True
    Next PT
Next ws


If Sheet3.Range("D56") > Sheet3.Range("D57") And Sheet3.Range("D56") > Sheet3.Range("D58") And Sheet3.Range("D56") > Sheet3.Range("D59") And Sheet3.Range("D56") > Sheet3.Range("D60") Then
    With ActiveWorkbook.SlicerCaches("Slicer_Fruits")
        .SlicerItems("Apple").Selected = True
        .SlicerItems("Banana").Selected = False
        .SlicerItems("Other").Selected = False
        .SlicerItems("Orange").Selected = False
        .SlicerItems("Peach").Selected = False
    End With
    
    Sheet3.Range("A43:P43").Copy Destination:=Sheet1.Range("E18:T18")


ElseIf Sheet3.Range("D57") > Sheet3.Range("D56") And Sheet3.Range("D57") > Sheet3.Range("D58") And Sheet3.Range("D57") > Sheet3.Range("D59") And Sheet3.Range("D57") > Sheet3.Range("D60") Then
    With ActiveWorkbook.SlicerCaches("Slicer_Fruits")
        .SlicerItems("Apple").Selected = False
        .SlicerItems("Banana").Selected = True
        .SlicerItems("Other").Selected = False
        .SlicerItems("Orange").Selected = False
        .SlicerItems("Peach").Selected = False
    End With
    
    Sheet3.Range("A44:P44").Copy Destination:=Sheet1.Range("E18:T18")
    
ElseIf Sheet3.Range("D58") > Sheet3.Range("D56") And Sheet3.Range("D58") > Sheet3.Range("D57") And Sheet3.Range("D58") > Sheet3.Range("D59") And Sheet3.Range("D558") > Sheet3.Range("D60") Then
    With ActiveWorkbook.SlicerCaches("Slicer_Fruits")
        .SlicerItems("Apple").Selected = False
        .SlicerItems("Banana").Selected = False
        .SlicerItems("Other").Selected = True
        .SlicerItems("Orange").Selected = False
        .SlicerItems("Peach").Selected = False
    End With
    
    Sheet3.Range("A45:P45").Copy Destination:=Sheet1.Range("E18:T18")


ElseIf Sheet3.Range("D59") > Sheet3.Range("D56") And Sheet3.Range("D59") > Sheet3.Range("D57") And Sheet3.Range("D59") > Sheet3.Range("D58") And Sheet3.Range("D59") > Sheet3.Range("D60") Then
    With ActiveWorkbook.SlicerCaches("Slicer_Fruits")
        .SlicerItems("Apple").Selected = False
        .SlicerItems("Banana").Selected = False
        .SlicerItems("Other").Selected = False
        .SlicerItems("Orange").Selected = True
        .SlicerItems("Peach").Selected = False
    End With
    
    Sheet3.Range("A46:P46").Copy Destination:=Sheet1.Range("E18:T18")
  
    ElseIf Sheet3.Range("D60") > Sheet3.Range("D56") And Sheet3.Range("D60") > Sheet3.Range("D57") And Sheet3.Range("D60") > Sheet3.Range("D58") And Sheet3.Range("D60") > Sheet3.Range("D59") Then
    With ActiveWorkbook.SlicerCaches("Slicer_Fruits")
        .SlicerItems("Apple").Selected = False
        .SlicerItems("Banana").Selected = False
        .SlicerItems("Other").Selected = False
        .SlicerItems("Orange").Selected = False
        .SlicerItems("Peach").Selected = True
    End With
    
    Sheet3.Range("A46:P46").Copy Destination:=Sheet1.Range("E18:T18")


End If
 For Each ws In wb.Sheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = False
    Next PT
Next ws