New sheet with MAX cell values from other sheets

[rodmorais]

Hello my dear friends, 1st post here..

I have a spreadsheet with several sheets where sheet name is the person's name (1st and last name). I need create a new sheet with consolidated data, where column A already have the person's name, and I need the MAX cell value from columns A-C at each sheet's person.

So for example, I will get the MAX cell value from sheets John Lennon, George Harrison, Paul McCartney columns B-C and populate the new sheet like below:

New Sheet
[Column A]-----------[Column B]------------------------------------[Column C]
John Lennon [max cell value from B at John's sheet]-------[max cell value from C at John's sheet]
George Harrison
Paul McCartney

Thanks in advance!

Cheers from Brazil.
RodMorais

 

[Mentor82]

Hi,
Welcome to MrExcel .
Here I've prepared some code for you. First of all add a new worksheet manually in the spreadsheet and name it "Summary". Than copy and paste below code. Once you run it all the max values from columns B and C and sheed names will be gathered from all worksheets and returned to the Summary sheet starting from row 2. Each run of the macro clears the data in the summary tab before importing them from all worksheets.
Check if that what you need and let me know.

Sub GetMaxOfWorksheets()
    Dim noRow&
    Dim ws As Worksheet
    Dim shtSummary As Worksheet
    Dim str$
    
    Const shtMaster = "Summary"
    Const defCol$ = "A"
    noRow = 1
    
    Set shtSummary = Worksheets(shtMaster)
    
    'Data clearance
    shtSummary.Range("A2:C" & Cells.Rows.Count).ClearContents
    
    For Each ws In Worksheets
        If LCase(ws.Name) <> LCase(shtSummary.Name) Then
            noRow = noRow + 1
            str = "'" & ws.Name & "'!"
            shtSummary.Cells(noRow, defCol) = ws.Name
            shtSummary.Cells(noRow, defCol).Offset(0, 1) = WorksheetFunction.Max(Range(str & "B:B"))
            shtSummary.Cells(noRow, defCol).Offset(0, 2) = WorksheetFunction.Max(Range(str & "C:C"))
        End If
    Next ws
    
    MsgBox "Done", vbInformation, "InfoLog"
    
End Sub

 

[rodmorais]

Hey! Thank you very much!! I will definitely try that! Do you think is possible to do it without a script?

 

[Mentor82]

Hi,
You're welcome!. You need a list of worksheets and it's max values in column B and C and it's impossible to achieve it using standard functions in excel. For this reason you need a script.