Find Max Value in one worksheet and return the value to a cell in another

[sharc316]

Hi,

Been searching for some help with this issue to:

1) Look up max value in a column (number of rows varies) on worksheet B
2) Then return the the max value to a cell on worksheet A

Please help.

[jolivanes]

You might try one of these.
Change references as required.

Sub Maybe_A()
    Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("E:E"))
End Sub

Sub Maybe_B()
    Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Large(Sheets("Sheet2").Range("E:E"), 1)
End Sub

[mdmackillop]

Or by formula =MAX(Sheet2!E:E)

[sharc316]

Thank you jolivanes. I've used method A and it works. The max value that I'm pulling is a date. How would I format the destination cell ("D5") to date and have it display without slashes: ex, 031517

[jolivanes]

Your Cell D5 can be custom formatted as mmddyy
or

Sub Maybe_A()
    With Sheets("Sheet2").Range("D5")
        .Value = WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
        .NumberFormat = "mmddyy"
    End With
End Sub

or

Sub Maybe_B()
Dim a As Date
a = Application.WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
    Sheets("Sheet2").Range("D5").Value = "'" & Format(a, "mmddyy", "000000")
End Sub

[sharc316]

The destination cell format displays without slashes ex: 031517. I'm using this cell to concatenate with another cell and the slashes come back in the concatenated cell ex: companyname 03/15/17. Any way to remove slashes from the concatenated cell? I'm using this cell as the file name.

[jolivanes]

If you want to use the date in a filename, why not delete the slashes at that time like after concatenating?
That way you need to remove the slashes once only.

If the Company name is in cell D2 and the date with slashes in in cell C7, this will give you want you indicated you need.

Sub test()
    MsgBox Replace((Range("D2").Value & " " & Range("C7").Value), "/", "")
End Sub

[sharc316]

Thank you for your help.