Limit number of rows pasted with pastespecial

Hello - I have one spreadsheet (1000 rows, 30 columns) where users filter down the data until they have the set of students_ids (Column A) that they want to work with.

Then they click a button which runs the code below to copy the student_id's from the current spreadsheet to another spreadsheet (Test Transfer Data.xls).

What I would like to do is modify the code to only paste the first 10 id's. So the maximum it will ever paste is 10 ids (even if they filter down to 100 rows) Similar to a select top 15.......

Thank in advance!!

Please Login or Register  to view this content.

I may be missing something but if your range always start at A5 and you only want 10 values copied why dont you just set the range A5:A15? Maybe I am missing something though.

Sorry for the confusion.
The sheet is filtered so the original sheet contains 1000 rows but is filtered down to 20 rows (the other 1980 are hidden).

The paste correctly only pastes the 20 visible rows but I want to only select the first 15.

So I have to list A1:A1000 (because the filter might be looking at rows 10, 15, 150, 755, 756, 950, etc.) but cannot figure out how to limit the results to 10 rows.

I think this should work for you. The i used SpecialCells to select all the filtered values ie all the visible ones. I was thinking that you could just remove them after they are copied over to the new sheet. Depending on how the new sheet is set up this may cause some conflicts but seeing as how you wrote the macro initially i am sure you can adjust tailor it to fit your needs.

Please Login or Register  to view this content.