vba code

[Haydo]

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

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

 

[My Aswer Is This]

Please just tell us in words what your wanting to do here.
Lets not talk about this code. Just tell us in detail what your attempting to do.

 

[Haydo]

This code works well. I just need a small change to it.

There are hundreds of rows on the sheet i am working from called 'test sheet'

It takes the 5 lowest numbers (each number has decimal places) from columns Q and H, and takes the corresponding entry (name) from Column C and creates a new sheet for each result of Q and H

Q sheet result looks like this format
brad 23.18
steve 23.28
tom 24.24
Ian 24.58
Con 24.98


H sheet result looks like this
Maddy 13.23
Sarah 13.58
Missy 13.98
Zoe 14.54
Lily 14.89

So currently there are 2 new sheets being created, each with 5 rows and 2 columns

I would there to be only one sheet, with 2 rows separating each 5 row result

It would look like this..
brad 23.18
steve 23.28
tom 24.24
Ian 24.58
Con 24.98


Maddy 13.23
Sarah 13.58
Missy 13.98
Zoe 14.54
Lily 14.89

thanks in advance

 

[tonyyy]

Haydo,

The following is untested, but you might give it a try...

Private Sub CommandButton1_Click()
Dim sheetNum As Integer, i As Integer, row As Integer
Dim r As Range, time As Range
Dim tmp As Variant

sheetNum = ThisWorkbook.Worksheets.Count
If sheetNum = 1 Then Sheets.Add After:=Worksheets(Worksheets.Count)

Worksheets(1).Name = "test sheet"
Worksheets(2).Name = "Fastest QandH"
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 = 8 To 12
    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, 8).Value
    tmp(row, 1) = WorksheetFunction.Max(tmp)
Next i
End Sub

Cheers,

tonyyy