moving data to sheet 2

Status
Not open for further replies.

SAMJ

New Member
Joined
Jan 27, 2005
Messages
6
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
hi,

not sure if I understand correctly

Code:
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,148,216
Messages
5,745,436
Members
423,951
Latest member
peggrif

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top