Attribute VB_Name = "ImportWTXtable"
Sub SummaryTableImport_MultipleLaps()
Attribute SummaryTableImport_MultipleLaps.VB_Description = "Pastes wtx summary table into selected space on runsheet (need to manually select the starting box for the macro to detect where to paste the data)"
Attribute SummaryTableImport_MultipleLaps.VB_ProcData.VB_Invoke_Func = "w\n14"
'
' SummaryTableImport Macro
' Pastes wtx summary table into selected space on runsheet (need to manually select the starting box for the macro to detect where to paste the data)
' You can't undo macros so make sure you've selected the right box and saved your runsheet beforehand!
' If clipboard doesn't contain wintax table it won't work

' ** 16/01/2024 Add clear clipboard
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    
    Dim PasteOrigin As String
    Dim LastRow As Double

    Dim SummaryTable(0 To 13) As Double
    Dim i As Integer
    Dim n As Integer
    
    Dim SheetName As String
    Dim TestSheet As Integer
    
    SheetName = ActiveSheet.Name
    TestSheet = InStr(SheetName, "R")
    
    If TestSheet = 0 Then
        
        Dim ValidCells() As Variant
        ValidCells = Array("$U$12", "$U$26", "$U$40", "$U$54", "$U$68", "$U$82", "$U$96", "$U$110", "$U$124", "$U$138", "$U$152", "$U$166")
        
        '   Store selected cell as table paste origin point
        PasteOrigin = ActiveCell.Address
        'MsgBox (ActiveCell.Address)
        
        If IsInArray(PasteOrigin, ValidCells) = True Then
           
           '   Paste WTX table to end of runsheet
           Range("FI5").Select
           ActiveSheet.Paste
           
           '   Find last row of table
           LastRow = Range("FI" & Rows.Count).End(xlUp).Row
           'MsgBox (LastRow)
           
           '   Assign values to variables
           SummaryTable(0) = (WorksheetFunction.Average(Range("FK5:FK" & LastRow))) / 100 'BrkBias
           SummaryTable(1) = WorksheetFunction.Average(Range("FM5:FM" & LastRow)) 'WaterT
           SummaryTable(2) = WorksheetFunction.Average(Range("FN5:FN" & LastRow)) 'OilT
           SummaryTable(3) = WorksheetFunction.Average(Range("FO5:FO" & LastRow)) 'AirT
           SummaryTable(4) = WorksheetFunction.Average(Range("FS5:FS" & LastRow)) 'USOS Slow
           SummaryTable(5) = WorksheetFunction.Average(Range("FT5:FT" & LastRow)) 'USOS Med
           SummaryTable(6) = WorksheetFunction.Average(Range("FU5:FU" & LastRow)) 'USOS Fast
           SummaryTable(7) = WorksheetFunction.Average(Range("FV5:FV" & LastRow)) 'USOS Entry
           SummaryTable(8) = WorksheetFunction.Average(Range("FW5:FW" & LastRow)) 'USOS Mid
           SummaryTable(9) = WorksheetFunction.Average(Range("FX5:FX" & LastRow)) 'USOS Exit
           SummaryTable(10) = WorksheetFunction.Average(Range("FP5:FP" & LastRow)) 'FA LOCK
           SummaryTable(11) = WorksheetFunction.Average(Range("FQ5:FQ" & LastRow)) 'RA LOCK
           SummaryTable(12) = WorksheetFunction.Average(Range("FR5:FR" & LastRow)) 'WHL SPIN
           SummaryTable(13) = WorksheetFunction.Average(Range("FY5:FY" & LastRow)) 'Stability index
           
           ' Paste array into originally selected table
           For i = 0 To 3
               Range(PasteOrigin).Offset(0, i).Value = SummaryTable(i)
           Next
           
           Range(PasteOrigin).Offset(0, 4).Value = SummaryTable(4)
           Range(PasteOrigin).Offset(0, 6).Value = SummaryTable(5)
           Range(PasteOrigin).Offset(0, 8).Value = SummaryTable(6)
           Range(PasteOrigin).Offset(0, 9).Value = SummaryTable(7)
           Range(PasteOrigin).Offset(0, 10).Value = SummaryTable(8)
           Range(PasteOrigin).Offset(0, 11).Value = SummaryTable(9)
           Range(PasteOrigin).Offset(2, 8).Value = SummaryTable(10)
           Range(PasteOrigin).Offset(2, 9).Value = SummaryTable(11)
           Range(PasteOrigin).Offset(2, 10).Value = SummaryTable(12)
           Range(PasteOrigin).Offset(2, 11).Value = SummaryTable(13)
           
           ' Clear pasted table
           Range("FI:GF").Clear
       
        Else
        MsgBox ("Paste location not valid!")
       
       End If
       
       Else
        '   Race table location (fixed)
        PasteOrigin = "$W$12"
        
        '   Paste WTX table to end of runsheet
        Range("FI5").Select
        ActiveSheet.Paste
        
        '   Find last row of table
        LastRow = Range("FI" & Rows.Count).End(xlUp).Row
        'MsgBox (LastRow)
        
        '   Assign values to variables
        SummaryTable(0) = (WorksheetFunction.Average(Range("FK5:FK" & LastRow))) / 100 'BrkBias
        SummaryTable(1) = WorksheetFunction.Average(Range("FM5:FM" & LastRow)) 'WaterT
        SummaryTable(2) = WorksheetFunction.Average(Range("FN5:FN" & LastRow)) 'OilT
        SummaryTable(3) = WorksheetFunction.Average(Range("FO5:FO" & LastRow)) 'AirT
        SummaryTable(4) = WorksheetFunction.Average(Range("FS5:FS" & LastRow)) 'USOS Slow
        SummaryTable(5) = WorksheetFunction.Average(Range("FT5:FT" & LastRow)) 'USOS Med
        SummaryTable(6) = WorksheetFunction.Average(Range("FU5:FU" & LastRow)) 'USOS Fast
        SummaryTable(7) = WorksheetFunction.Average(Range("FV5:FV" & LastRow)) 'USOS Entry
        SummaryTable(8) = WorksheetFunction.Average(Range("FW5:FW" & LastRow)) 'USOS Mid
        SummaryTable(9) = WorksheetFunction.Average(Range("FX5:FX" & LastRow)) 'USOS Exit
        SummaryTable(10) = WorksheetFunction.Average(Range("FP5:FP" & LastRow)) 'FA LOCK
        SummaryTable(11) = WorksheetFunction.Average(Range("FQ5:FQ" & LastRow)) 'RA LOCK
        SummaryTable(12) = WorksheetFunction.Average(Range("FR5:FR" & LastRow)) 'WHL SPIN
        SummaryTable(13) = WorksheetFunction.Average(Range("FY5:FY" & LastRow)) 'Stability index
        
        ' Paste array into originally selected table
        For i = 0 To 8
            Range(PasteOrigin).Offset(0, i).Value = SummaryTable(i)
        Next
        Range(PasteOrigin).Offset(0, 10).Value = SummaryTable(9)
        Range(PasteOrigin).Offset(2, 6).Value = SummaryTable(10)
        Range(PasteOrigin).Offset(2, 7).Value = SummaryTable(11)
        Range(PasteOrigin).Offset(2, 8).Value = SummaryTable(12)
        Range(PasteOrigin).Offset(2, 10).Value = SummaryTable(13)
        
        ' Clear pasted table
        Range("FI:GF").Clear
    
    End If
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       
    End Sub
    
    Sub SummaryTableImport_SingleLap()
    '
    ' SummaryTableImport Macro
    ' Pastes wtx summary table into selected space on runsheet (need to manually select the starting box for the macro to detect where to paste the data)
    ' You can't undo macros so make sure you've selected the right box and saved your runsheet beforehand!
    ' If clipboard doesn't contain wintax table it won't work
    
   Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    
    Dim PasteOrigin As String
    Dim LastRow As Double

    Dim SummaryTable(0 To 9) As Double
    Dim i As Integer
    Dim n As Integer
    
    Dim SheetName As String
    Dim TestSheet As Integer
    
    SheetName = ActiveSheet.Name
    TestSheet = InStr(SheetName, "R")
    
    If TestSheet = 0 Then
        
        Dim ValidCells() As Variant
        ValidCells = Array("$U$12", "$U$26", "$U$40", "$U$54", "$U$68", "$U$82", "$U$96", "$U$110", "$U$124", "$U$138", "$U$152", "$U$166")
        
        '   Store selected cell as table paste origin point
        PasteOrigin = ActiveCell.Address
        'MsgBox (ActiveCell.Address)
        
        If IsInArray(PasteOrigin, ValidCells) = True Then
           
           '   Paste WTX table to end of runsheet
           Range("FI5").Select
           ActiveSheet.Paste
           
           '   Find last row of table
           LastRow = Range("FI" & Rows.Count).End(xlUp).Row
           'MsgBox (LastRow)
           
        '   Assign values to variables
           SummaryTable(0) = Range("FK" & LastRow) / 100 'BrkBias
           SummaryTable(1) = Range("FM" & LastRow) 'WaterT
           SummaryTable(2) = Range("FN" & LastRow) 'OilT
           SummaryTable(3) = Range("FO" & LastRow) 'AirT
           SummaryTable(4) = Range("FS" & LastRow) 'USOS Slow
           SummaryTable(5) = Range("FT" & LastRow) 'USOS Med
           SummaryTable(6) = Range("FU" & LastRow) 'USOS Fast
           SummaryTable(7) = Range("FV" & LastRow) 'USOS Entry
           SummaryTable(8) = Range("FW" & LastRow) 'USOS Mid
           SummaryTable(9) = Range("FX" & LastRow) 'USOS Exit
           
           ' Paste array into originally selected table
           For i = 0 To 3
               Range(PasteOrigin).Offset(0, i).Value = SummaryTable(i)
           Next
           
           Range(PasteOrigin).Offset(0, 4).Value = SummaryTable(4)
           Range(PasteOrigin).Offset(0, 6).Value = SummaryTable(5)
           Range(PasteOrigin).Offset(0, 8).Value = SummaryTable(6)
           Range(PasteOrigin).Offset(0, 9).Value = SummaryTable(7)
           Range(PasteOrigin).Offset(0, 10).Value = SummaryTable(8)
           Range(PasteOrigin).Offset(0, 11).Value = SummaryTable(9)
           
           ' Clear pasted table
           Range("FI:GF").Clear
       
        Else
        MsgBox ("Paste location not valid!")
       
       End If
       
       Else
        '   Race table location (fixed)
        PasteOrigin = "$W$12"
        
        '   Paste WTX table to end of runsheet
        Range("FI5").Select
        ActiveSheet.Paste
        
        '   Find last row of table
        LastRow = Range("FI" & Rows.Count).End(xlUp).Row
        'MsgBox (LastRow)
        
    '   Assign values to variables
       SummaryTable(0) = Range("FK" & LastRow) / 100 'BrkBias
       SummaryTable(1) = Range("FM" & LastRow) 'WaterT
       SummaryTable(2) = Range("FN" & LastRow) 'OilT
       SummaryTable(3) = Range("FO" & LastRow) 'AirT
       SummaryTable(4) = Range("FS" & LastRow) 'USOS Slow
       SummaryTable(5) = Range("FT" & LastRow) 'USOS Med
       SummaryTable(6) = Range("FU" & LastRow) 'USOS Fast
       SummaryTable(7) = Range("FV" & LastRow) 'USOS Entry
       SummaryTable(8) = Range("FW" & LastRow) 'USOS Mid
       SummaryTable(9) = Range("FX" & LastRow) 'USOS Exit
        
        ' Paste array into originally selected table
        For i = 0 To 8
            Range(PasteOrigin).Offset(0, i).Value = SummaryTable(i)
        Next
        Range(PasteOrigin).Offset(0, 10).Value = SummaryTable(9)
        
        ' Clear pasted table
        Range("FI:GF").Clear
    
    End If
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
End Sub

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function