Pull random rows from other sheets to summary sheet

Hi folks,

I'm trying to write code for a summary sheet which will pull the first four cells from a random rows in the other sheets in the workbook.

So for example there will be three spreadsheets A, B and C.

From spreadsheet A I need the first four cells from the last row
From spreadsheet B I need the first four cells from the second last row
From C I need the first four cells from the middle row.

So how do I get these values from within each sheet, and then I have them pulled out automatically from another sheet.

Would it be easier if I copied the whole row? I could do that...

Please let me know your thoughts, many thanks,

AR

Originally Posted by jaslake

Hi agentred
Are the rows truly random or will you ALWAYS need the last row from sheet A and ALWAYS need the second last row from sheet B? How many rows are there in sheet C? How do you define "middle row"?
John

Hi John, thanks for the reply.

Well, this is how it goes... In sheet A, I have a list of how many items I bought. Then a few blank rows, and a summary row, then a few more blank rows and then another summary row.

Within sheet A I use the COUNTA function to define a variable x which counts how many items I bought, then I just use x +3 and x + 5 for the summary rows.

I want to pull out the summary rows, so while they'll always be x + 3 and x + 5, the value of x itself will change. Sheets A B and C are automatically created from another macro.

I use the exact same process in sheets B and C.

Does that explain what I'm trying to do? I hope it does, many thanks for your help,

AR

I just used the middle as an example, it's not relevant.

I think I'll always need to pull out the last row and the fourth last row. (so if there are 10 rows, I'll need row 10 and row 6, but don't care about 7-9), Does that help? Gracias,

Hey,

That works perfectly actually, thank you! It also shows me how to select cells from a variable row index, which is something I've been trying to figure out for a while.

You're right about arrangements though, is it possible to arrange it so that it's

"Last Row Summary"
Sheet A's last row
Sheet B's last row

"Fourth Last Row Summary"
Sheet A's fourth last row
Sheet B's fourth last row

It's easy to have it the other way around (Sheet A summary, Sheet B Summary) because the if function cycles through each sheet but I can't figure out how to do it otherwise. I tried replacing the .end(xlup) with fixed references but it didn't work it.

This is just a corollary, the main part is done and you have my most sincere gratitude,

AR