Recurring Fetching in a summary table MIN, MAX, AVERAGE for a specific data set in consecutive works-CodePudding

How do I reduce manual work in excel to fetch in a summary table a recurring use of MIN, MAX, AVERAGE for a specific data set in consecutive worksheets of a workbook. The dataset will be different in each worksheet, but the three operations have to be performed on each of the dataset in the column of each excel tab. The summary represents the cycle time minimum, maximum and averages of rubber batches being produced in a factory over time. There are approximately 50 different rubber products, so 50 worksheets in the workbook.Cycle Time with Control charts - Query.
I have provided sample data in the link.

CodePudding user response：

Here is a script which creates a new sheet as sheet 1, calls it Summary, puts in the column headings and runs through all the sheets.

Option Explicit

Sub makeSummary()

Worksheets.Add Before:=Sheets(1)
Sheets(1).Name = "Summary"
Sheets(1).Range("A1").Value = "Sheet N°"
Sheets(1).Range("B1").Value = "Sheet Name"
Sheets(1).Range("C1").Value = "Minimum"
Sheets(1).Range("D1").Value = "Maximum"
Sheets(1).Range("E1").Value = "Average"

Dim i As Integer
For i = 2 To Sheets.Count
    CopySheet (i)
Next
End Sub

Sub CopySheet(s)  ' sheetNumber to treat

Dim minVal, maxVal, Running, i As Long
minVal = 100000
i = 3
Do While Sheets(s).Range("C" & i).Value <> 0
    If (Sheets(s).Range("C" & i).Value < minVal) Then
        minVal = Sheets(s).Range("C" & i).Value
    End If
        If (Sheets(s).Range("D" & i).Value > maxVal) Then
        maxVal = Sheets(s).Range("D" & i).Value
    End If
    Running = Running   Sheets(s).Range("E" & i).Value
    i = i   1
Loop

Sheets(1).Range("A" & s).Value = s
Sheets(1).Range("B" & s).Value = Sheets(s).Name
Sheets(1).Range("C" & s).Value = minVal
Sheets(1).Range("D" & s).Value = maxVal
Sheets(1).Range("E" & s).Value = Running / (i - 3)

End Sub

and the output from the test sheets (I've named the sheets with 'product names'. You could also copy this from a cell in the sheet if there is one.)

Sheet N°    Sheet Name  Minimum Maximum Average
2           ProductA    2       150     35
3           ProductB    1       177     40