Summarizing max of 2 columns from multiple sheets

chachie22

New Member
Joined
Mar 9, 2015
Messages
11
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.

VBA Code:
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

data1max from column 1max from column 2
data2max from column 1max from column 2
data3max from column 1max 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

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

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

VBA Code:
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
 

Forum statistics

Threads
1,144,341
Messages
5,723,811
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top