Reading "data" in a sheet and applying to another sheet

I am relatively new to this stuff but can do the basics. What I am looking to do is for where I work and will make my job MUCH easier when I audit logs. Here's the scenario:

I have a workbook with 3 sheets. "Sheet1", "Sheet 2", and "Sheet 3". I have run in to a snag in my planning. The part I can't seem to find anywhere is on the "Sheet 2" sheet I'm generating random numbers using the "=ROUND(RAND()*2700+1,0)" command. What I need to do is read the result of that equation (after exporting/importing to remove the formatting) and apply it to the "Sheet 1" sheet. In a nutshell. If the equation comes back with 2483 then I want to select all of row 2483 on "Sheet 1" and copy it to a specific line on the "Sheet 3" sheet (increasing by 1 each time this happens as not to over write the previous entry. Any ideas on how to do this efficiently?

Thanks a million!!

Hello Kollinsb,

It isn't clear from your post how "Sheet2" is setup. This macro will copy an entire row data from "Sheet1" to "Sheet3". You will need to change the StartRow variable from 1 to the row you want the copied row to start at. You will need to add a Standard VBA Module to your workbook and the paste the macro code into it. Select the row on "Sheet1" to copy. The macro can then be run manually using ALT+F8 to bring up the Run Macro Dialog.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thank you for your quick reply and input.

This will probably take care of a portion of the issue.

Let's assume "Sheet 2" has a numeric value in A1 through A274 (I can get this far without errors). That value will be a whole number between 1 and 2733 (274 is approximately 10% of the total of 2733). I need to read the number in each row, use the number in that row to select that row on "Sheet 1" and copy the entire row from "Sheet 1" to "Sheet 3" on a row that increases by one every time this is done.

The end result is a log audit. I have been tasked for the past year or more with auditing 10% of our logs. They must be selected randomly.

I don't have the actual code on this machine but here are the steps of what it does (or should do in the end):
1. Import data from a database in to "Sheet 1"
2. Generate random numbers on "Sheet 2" by using the "=round(rand()*2733+1,0)" command and copy that formula and paste it, starting with A2 for at least 10% of the total returned. (274 in this example) (where 2733 is the number of items returned from the database query)
3. So, A1 though A274 now contain whole numbers that will not exceed 2733.
4. For each entry in "Sheet 2", A1:A274, the number displayed there should be the actual row number on "Sheet 1". For example, if, on "Sheet 2", cell A188 contains the number 2730, Row 2730 on "Sheet 1" should be set to a yellow background, copied and pasted to cell A188 of "Sheet 3". This process will be the same until it reaches cell A275. When that happens, the macro is complete.

Does that help or am I just confusing the issue?

Hello Kollinsb,

Thank you, that explains it perfectly. I will recode the macro to read column "A" and copy the rows based on the cell's value.

Thank you,
Leith Ross

Hello Kollinsb,

Here is the revised macro. After it is installed in Standard Module, you can add a Form toolbar command button to worksheet 2 and assign the macro to it. Click the button and all the rows will be copied.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Excellent. I'm not where I can try it right now but I really appreciate it and will try it this weekend. You have no idea.

I haven't forgot about letting everyone know I've just been hammered at work. I WILL post when I know if it works for me....

Thank you!!

I tested it and it works like a charm. Makes my life so much easier.

THANK YOU!!!