With the code below, the results for Q and H are separately sent to 2 new sheets.
How can i get this code to send both Q and H results to just 1 new sheet, to appear in columns A and B, with a few blank rows separating both results?
Is there a way to write this code more efficiently?
Thanks
How can i get this code to send both Q and H results to just 1 new sheet, to appear in columns A and B, with a few blank rows separating both results?
Is there a way to write this code more efficiently?
Thanks
Code:
Private Sub CommandButton1_Click()
Dim sheetNum, i, row As Integer
Dim r, time As Range
Dim tmp As Variant
sheetNum = ThisWorkbook.Worksheets.Count
If sheetNum = 1 Then
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets.Add After:=Worksheets(Worksheets.Count)
ElseIf sheetNum = 2 Then
Sheets.Add After:=Worksheets(Worksheets.Count)
End If
Worksheets(1).Name = "test sheet"
Worksheets(2).Name = "Fastest Q"
Worksheets(3).Name = "Fastest H"
Set r = Worksheets(1).UsedRange
Set time = r.Columns(17)
tmp = time.Value
For i = 1 To 5
row = WorksheetFunction.Match(WorksheetFunction.Small(tmp, 1), tmp, 0)
Worksheets(2).Cells(i, 1).Value = Worksheets(1).Cells(row, 3).Value
Worksheets(2).Cells(i, 2).Value = Worksheets(1).Cells(row, 17).Value
tmp(row, 1) = WorksheetFunction.Max(tmp)
Next i
Set time = r.Columns(8)
tmp = time.Value
For i = 1 To 5
row = WorksheetFunction.Match(WorksheetFunction.Small(tmp, 1), tmp, 0)
Worksheets(3).Cells(i, 1).Value = Worksheets(1).Cells(row, 3).Value
Worksheets(3).Cells(i, 2).Value = Worksheets(1).Cells(row, 8).Value
tmp(row, 1) = WorksheetFunction.Max(tmp)
Next i
End Sub