VBA Copy a Varying Number of Whole Rows

I have a workbook that will have information about different users and am trying to copy the top users in different categories to another sheet.

Right now, I have my macro set to go into the worksheet and sort the workbook based on the criteria I am interested in. I have the number of "top users" on a different sheet, and I want to be able to select the number of rows that corresponds to the number of top users, copy it, and paste it into another sheet.

For example, one of the categories is Questions Asked. For a specific date, there were 49 top question askers (this value is given in cell F31 on the sheet "Question Distribution"). In the sheet where all the data is, "Daily Activity," I have the macro sort the question category, and then I want to select the top 49 rows, copy the information in those rows, and paste them to a new sheet "Top Questions". But should the value in cell F31 change, I want it to select that many rows instead.

Is this possible?

Submit a copy of your workbook.

As it contains sensitive information, I cannot. I'll see if I can put together a similar one. But isn't it possible to answer this without the actual workbook?

I'm basically looking to select a number of rows [such as with rows("2:50").select ] except instead of the range being 2:50, it being 2: (the value given in cell F31 on a different worksheet).

Possible to answer? Yes. Do I want to take the time to create a worksheet matching all the parameters you put in place so I can test the code to make sure it works? No.

So with this sample workbook, I want to be able to have the macro go to the Activity sheet, sort descending by questions, and then select rows 2:The value given by cell D3 in the Top Users sheet and then paste those rows to the Top Questions sheet.

Similarly, I will want it to sort descending by answers, select rows 2:The value given by cell D4 in the Top users sheet, and paste those into the Top Answers sheet.

Etc etc

Try this:

Please Login or Register  to view this content.

Seems to work great! Thanks so much. The range("D3").value command is the command I was really looking for. Pretty neat stuff