trying to sort out data

[quapple]

I have a long table of data, the first column is the sample ID (in yellow, red) and the corresponding rows are the data associated with that sample ID (see photo below). I want to extract data in between a certain sample ID range, for example, I want to take the Au, Ag, AI etc. data associated with sample IDs from 10000 and 10999 and put this in a new sheet. Can this be done with vlookup?

 

[mrshl9898]

If you made a list of the SampleIDs you could VLOOKUP each column individually, but it's very inefficient.

This code will let you type numbers to search between and create the sheet for you

Sub movedata()

Dim rownum As Long
Dim rownum2 As Long
Dim fromVal As Long
Dim toVal As Long
Dim ws As Worksheet
Dim ws2 As Worksheet

fromVal = InputBox("Enter Value to Search From")
toVal = InputBox("Enter Value to Search To")
rownum = 6
rownum2 = 6
Set ws = ActiveSheet
Sheets.Add
ActiveSheet.Name = ("Paste Sheet")
Set ws2 = Sheets("Paste Sheet")

ws.Rows("2:5").Copy ws2.Rows("2:5")

Do Until ws.Cells(rownum, 1) = ""
    If ws.Cells(rownum, 1) >= fromVal And ws.Cells(rownum, 1) <= toVal Then
        ws.Rows(rownum).Copy ws2.Rows(rownum2)
        rownum2 = rownum2 + 1
    End If
rownum = rownum + 1
Loop

End Sub