Copy an entire row using the small function

[anwaee2]

I have a range of B7:G37. I would like to copy the the cells in the row that has the result of Small(G7:G37,1) Into cells I6:U6. I have tried and tried and searched the internet to no avail. I would like to do this with just a formula.

Example: If cell G25 has the lowest number in the range of G7:G37, then I would like to copy B25:G25 to I6:U6. I'm sure there is a way but I can't figure it out.

Thanks in advance for any help, I know you all have been great in the past and I would like to thank all again.

 

[Takae]

Range B25:G25 has 6cells and Range I6:U6 has 13cells. So remainder cells( O6:U6 ) will be blank.

Sub test()
Dim rng As Range
Dim buf, c
Dim rn As Long


Set rng = Range("G7:G37")


buf = WorksheetFunction.Small(rng, 1)
For Each c In rng
    If c = buf Then
        rn = c.row
        Exit For
    End If
Next
Range("I6:N6").Value = Range(Cells(rn, 2), Cells(rn, 7)).Value
End Sub

 

[anwaee2]

Range B25:G25 has 6cells and Range I6:U6 has 13cells. So remainder cells( O6:U6 ) will be blank.

Sub test()
Dim rng As Range
Dim buf, c
Dim rn As Long


Set rng = Range("G7:G37")


buf = WorksheetFunction.Small(rng, 1)
For Each c In rng
    If c = buf Then
        rn = c.row
        Exit For
    End If
Next
Range("I6:N6").Value = Range(Cells(rn, 2), Cells(rn, 7)).Value
End Sub

Thank you so much. I had just figured out how to do it with formulas but if there were two values the ssame it would repeat the first value. Your solution worked great. I did add to it to also include the next 4 lowest values and placed it in a worksheet_change. Once again Mr Excel came through. Thanks again for your help.