Find max cell within range, copy cell, clear copied cell contents, paste on another sheet.

[TehTOECUTTER]

Hello,

I am having trouble determining the code for this logic:

-Select sheet2
-If Sum of CN16:CN24 > 0 Then
-Select cell with max value within range CN16:CN24
-Copy cell contents
-Clear cell contents (important)
-Select sheet1
-Paste copied value in cell G5

Thanks for any help!

 

[Joe4]

Try this:

Sub MyMacro()

    Dim myRange As Range
    Dim myMax As Double
    Dim cell As Range
    
    Set myRange = Sheets("Sheet2").Range("CN16:CN24")
    
    If Application.WorksheetFunction.Sum(myRange) > 0 Then
        myMax = Application.WorksheetFunction.Max(myRange)
        For Each cell In myRange
            If cell = myMax Then
                cell.ClearContents
                Sheets("Sheet1").Range("G5") = myMax
            End If
        Next cell
    End If
    
End Sub