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.
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
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
Could someone point me in the right direction? Thanks in advance
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
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
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