Attribute VB_Name = "CreateTemperatureStatistics"
Option Explicit

Public Sub BuildTemperatureStatisticsSheet()
    Const STATISTICS_SHEET As String = "Statistics"
    Const COMBINED_SHEET As String = "Combined Data"
    Const TEMPERATURE_HEADER As String = "Temperature"

    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim statisticsSheet As Worksheet
    Dim temperatureCell As Range
    Dim lastRow As Long
    Dim dataRow As Long
    Dim sensorCount As Long
    Dim readingCount As Long
    Dim values() As Double
    Dim outputData() As Variant
    Dim oldCalculation As XlCalculation

    Set sourceBook = ActiveWorkbook

    oldCalculation = Application.Calculation
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    On Error GoTo CleanFail

    'First pass: count valid sensor sheets.
    For Each sourceSheet In sourceBook.Worksheets
        If StrComp(sourceSheet.Name, STATISTICS_SHEET, vbTextCompare) <> 0 And _
           StrComp(sourceSheet.Name, COMBINED_SHEET, vbTextCompare) <> 0 Then

            Set temperatureCell = FindStatisticsHeading(sourceSheet, TEMPERATURE_HEADER)
            If Not temperatureCell Is Nothing Then
                sensorCount = sensorCount + 1
            End If
        End If
    Next sourceSheet

    If sensorCount = 0 Then
        MsgBox "No sheets containing a '" & TEMPERATURE_HEADER & "' heading were found.", _
               vbExclamation, "Temperature statistics"
        GoTo CleanExit
    End If

    ReDim outputData(1 To sensorCount, 1 To 6)
    sensorCount = 0

    'Second pass: calculate statistics for each sensor sheet.
    For Each sourceSheet In sourceBook.Worksheets
        If StrComp(sourceSheet.Name, STATISTICS_SHEET, vbTextCompare) <> 0 And _
           StrComp(sourceSheet.Name, COMBINED_SHEET, vbTextCompare) <> 0 Then

            Set temperatureCell = FindStatisticsHeading(sourceSheet, TEMPERATURE_HEADER)

            If Not temperatureCell Is Nothing Then
                lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, temperatureCell.Column).End(xlUp).Row
                readingCount = 0
                Erase values

                If lastRow > temperatureCell.Row Then
                    ReDim values(1 To lastRow - temperatureCell.Row)
                End If

                For dataRow = temperatureCell.Row + 1 To lastRow
                    If Len(Trim$(CStr(sourceSheet.Cells(dataRow, temperatureCell.Column).Value2))) > 0 Then
                        If IsNumeric(sourceSheet.Cells(dataRow, temperatureCell.Column).Value2) Then
                            readingCount = readingCount + 1
                            values(readingCount) = CDbl(sourceSheet.Cells(dataRow, temperatureCell.Column).Value2)
                        End If
                    End If
                Next dataRow

                sensorCount = sensorCount + 1
                outputData(sensorCount, 1) = sourceSheet.Name

                If readingCount > 0 Then
                    ReDim Preserve values(1 To readingCount)
                    outputData(sensorCount, 2) = Application.WorksheetFunction.Min(values)
                    outputData(sensorCount, 3) = Application.WorksheetFunction.Max(values)
                    outputData(sensorCount, 4) = Application.WorksheetFunction.Average(values)
                    outputData(sensorCount, 5) = Application.WorksheetFunction.Median(values)
                    outputData(sensorCount, 6) = Application.WorksheetFunction.StDev_P(values)
                Else
                    outputData(sensorCount, 2) = "No numeric readings"
                End If
            End If
        End If
    Next sourceSheet

    Application.DisplayAlerts = False
    On Error Resume Next
    sourceBook.Worksheets(STATISTICS_SHEET).Delete
    On Error GoTo CleanFail
    Application.DisplayAlerts = True

    Set statisticsSheet = sourceBook.Worksheets.Add(After:=sourceBook.Worksheets(sourceBook.Worksheets.Count))
    statisticsSheet.Name = STATISTICS_SHEET
    statisticsSheet.DisplayPageBreaks = False

    With statisticsSheet.Range("A1:F1")
        .Merge
        .Value = "Temperature Statistics"
        .Interior.Color = RGB(31, 78, 120)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
        .Font.Size = 16
        .RowHeight = 28
    End With

    With statisticsSheet.Range("A2:F2")
        .Merge
        .Value = "Statistics calculated from all numeric temperature readings on each sensor sheet. Standard deviation is population standard deviation."
        .Interior.Color = RGB(217, 234, 247)
        .Font.Color = RGB(51, 65, 85)
        .Font.Italic = True
        .WrapText = True
        .RowHeight = 32
    End With

    statisticsSheet.Range("A4").Value = "Sensor"
    statisticsSheet.Range("B4").Value = "Minimum"
    statisticsSheet.Range("C4").Value = "Maximum"
    statisticsSheet.Range("D4").Value = "Average"
    statisticsSheet.Range("E4").Value = "Median"
    statisticsSheet.Range("F4").Value = "Standard Deviation"

    statisticsSheet.Range("A5").Resize(sensorCount, 6).Value = outputData

    With statisticsSheet.Range("A4:F4")
        .Interior.Color = RGB(91, 155, 213)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With

    statisticsSheet.Range("B5:F" & (sensorCount + 4)).NumberFormat = "0.00"
    statisticsSheet.Columns("A").ColumnWidth = 25
    statisticsSheet.Columns("B:F").ColumnWidth = 18

    statisticsSheet.ListObjects.Add( _
        SourceType:=xlSrcRange, _
        Source:=statisticsSheet.Range("A4:F" & (sensorCount + 4)), _
        XlListObjectHasHeaders:=xlYes).Name = "TemperatureStatisticsTable"

    statisticsSheet.Activate
    ActiveWindow.FreezePanes = False
    statisticsSheet.Range("B5").Select
    ActiveWindow.FreezePanes = True
    statisticsSheet.Range("A1").Select

    MsgBox sensorCount & " sensor sheets were included in the statistics.", _
           vbInformation, "Temperature statistics"

CleanExit:
    Application.DisplayAlerts = True
    Application.Calculation = oldCalculation
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    Application.DisplayAlerts = True
    Application.Calculation = oldCalculation
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox "The statistics sheet could not be created:" & vbCrLf & vbCrLf & _
           Err.Description, vbCritical, "Temperature statistics"
End Sub

Private Function FindStatisticsHeading(ByVal targetSheet As Worksheet, _
                                       ByVal headingText As String) As Range
    Dim foundCell As Range

    Set foundCell = targetSheet.Cells.Find( _
        What:=headingText, _
        After:=targetSheet.Cells(targetSheet.Rows.Count, targetSheet.Columns.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    Set FindStatisticsHeading = foundCell
End Function