Creating a new table in a new worksheet with maximum column values from other worksheets

Question

Good evening, I am trying to load number of csv files and then calculate the max of each column from Column E to the last column (last column can be different for each run) and paste the values in a new work sheet.

I have tried to break my coading down in stages as I am learning VBA:

Step 1. Code to open folder select dialogue box and load each text files to excel in different worksheets with file name

Step 2. Code to open new worksheet and name as “Result”

with some sample data step 1 and 2 look like this [figure 1]:

and finally

Step 3. Code to find the maximum value for column E to last column and paste in the result sheet with the year number and the heigh number from the worksheet name, for example as follows:

So far with the help of this forum I have managed to create procedures with a simple userform with a run button that look like this:

I have manged to complete Step 1 and Step 2 (please see my codes below) but now really stuck with Step 3.

So far for step 3 I have managed to write something that can calculate max for column ‘E’ but just could not figure out how to calculate for all columns from column ‘E’ onwards for each work sheet and paste to the result sheet. I can calculate the col E max using the following code but the 5th line on the code do not copy to other columns:

Sub SumData()

    Dim lastrow As Long
       lastrow = Range("A1").End(xlDown).Row

    Cells(lastrow + 2, "E").Formula = "=MAX(E2:E" & lastrow & ")"

    Cells(lastrow + 2, "E").AutoFill , Type:=xlFillDefault

    End Sub 

I would really appreciate any advise with my step 3 and to make you understand easier I have copied my sample csv files in the following drop box link: https://www.dropbox.com/sh/hqhzd901vwlgbl9/AAApxVc_CAsESxR9iZ4cHoOua?dl=0

The codes that I have created for step 1 and 2 are below and working for me:

    Private Sub FilePath_Button_Click()
get_folder
End Sub



Private Sub Run_Button_Click()
load_file
End Sub

Public Sub get_folder()

Dim FolderName As String
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  .Show
  On Error Resume Next
  FolderName = .SelectedItems(1)
  Err.Clear
  On Error GoTo 0
End With
TextBox1.Text = FolderName
End Sub

Sub load_file()
Dim strFile As String
Dim WS As Worksheet
Dim test As String

Dim wb As Workbook

test = TextBox1.Text

strFile = Dir(Me.TextBox1.Text & "\*.plt")

       Set wb = Workbooks.Add
        'added workbook becomes the activeworkbook
       With wb
       Do While Len(strFile) > 0

        Set WS = ActiveWorkbook.Sheets.Add
        WS.Name = strFile

With WS.QueryTables.Add(Connection:= _
     "TEXT;" & test & "\" & strFile, Destination:=Range("$A$1"))
    .Name = strFile
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False

    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
strFile = Dir
Loop
End With

Dim WS2 As Worksheet
Set WS2 = Sheets.Add
Sheets.Add.Name = "Result"

MsgBox "Job Complete"
End Sub



Private Sub UserForm_Click()

End Sub

There may lot simpler way of writing the codes but this is the best I could come up with for step 1 and 2. Many thanks in advance!

Answer 1

If all the worksheet names has the same format i.e XXX_XXX_XX_XXXX then it is pretty simple to extract those values. You can use Split function. here is an example

Sub Sample()
    Dim sName(1 To 4) As String
    Dim i As Long

    sName(1) = "HP5_1gt_60_2010"
    sName(2) = "HP5_1gt_70_2010"
    sName(3) = "HP5_1gt_100_2008"
    sName(4) = "HP5_1gt_110_2008"

    For i = 1 To 4
        Debug.Print "Height --> " & Split(sName(i), "_")(2)
        Debug.Print "Year --> " & Split(sName(i), "_")(3)
        Debug.Print "-----"
    Next i
End Sub

Output

And to find the maximum value in a column, you can use the Max worksheet function in VBA. here is an example

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Columns(5)

    Debug.Print Application.WorksheetFunction.Max(rng)
End Sub

Output