moving data to sheet 2

[SAMJ]

Please Help!! I have sheet one with 4 columns and many rows of info. On sheet 2 I need to be albe to enter a random piece of info found in sheet 1 and have the remaining row information found there populate a table on sheet 2. Any ideas??? Any help would be GREATLY appreciated!!! Thanks Sam

 

[jindon]

hi,

not sure if I understand correctly

Sub SAMJ()
Dim firstR As Long, lastR As Long, a, ws1 As Worksheet, ws2 As Worksheet
Dim dic As Object, x As Long
Set dic = CreateObject("Scripting.Dictionary")
Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
firstR = 1 ' <-- change this if needed, row# from which the data begins
lastR = ws1.Range("a65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = firstR To lastR
check:
    x = Int((lastR * Rnd()) + firstR)
    If Not dic.Exists(x) Then
        dic.Add x, Nothing
    Else
        GoTo check
    End If
Next
a = dic.keys
With ws2
    .Cells.Clear
    lastR = ws2.Range("a65536").End(xlUp).Row
    For i = LBound(a) To UBound(a)  ' change n, resize(1,n), to # of columns
        ws1.Cells(a(i), 1).Resize(1, 4).Copy _
        Destination:=ws2.Cells(lastR + i + 1, 1)
    Next
End With
Application.ScreenUpdating = True
Set ws2 = Nothing
Set ws2 = Nothing
Set dic = Nothing
Erase a
End Sub

rgds,
jindon

 

[Fergus]

Hi Sam,

How random will the piece of information that you enter be, and where will it be entered? If you want to enter in the first column of your table on Sheet2 something that can be found in the first column of your Sheet1 and then have the remaining three columns of that row populated from Sheet1 then you could use INDEX / MATCH. or VLOOKUP. Post back if you want to know more.

 

[Von Pookie]

Continued here:
http://www.mrexcel.com/board2/viewtopic.php?t=127836