Summarizing max of 2 columns from multiple sheets

[chachie22]

Good afternoon,

I am using the following VBA to generate a new workbook with a new sheet based on whichever txt file the user selects. For example, if the user selects data1.txt, data2.txt and data3.txt, a new workbok will be created with 3 sheets called data1, data2, data3.

Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=True, _
      Other:=True, OtherChar:="|"
    x = x + 1

    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=False, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=False, _
              Other:=False, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

I would like to add a code that would find the max value of two columns for each sheet and summarize them in a single, separate worksheet. Ideally it would end up looking something like

data1	max from column 1	max from column 2
data2	max from column 1	max from column 2
data3	max from column 1	max from column 2

I am not the best at VBA but I found the following code which seems like it may do the trick; I just can't get it to work

Sub getMax()
Dim sh As Worksheet, mx As Variant
mx = 0
For Each sh In ThisWorkbook.Sheets
    If sh.Name Like "*" Then
        If Application.Max(Range("B:B")) > mx Then
            mx = Application.Max(Range("B:B"))
        End If
    End If
Next
Sheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) = mx
End Sub

Could someone point me in the right direction? Thanks in advance

 

[Saurabhj]

Hi,

Check below code:
I have created a separate sheet "summary" in the workbook.

Sub getMax()
Dim sh As Worksheet, rowno As Integer
rowno = 1
For Each sh In ThisWorkbook.Sheets
    If sh.Name Like "data*" Then
        Sheets("summary").Cells(rowno, "A") = sh.Name
        Sheets("summary").Cells(rowno, "B") = WorksheetFunction.Max(sh.Range("A:A"))
        Sheets("summary").Cells(rowno, "C") = WorksheetFunction.Max(sh.Range("B:B"))
        rowno = rowno + 1
    End If
Next

End Sub