Copy/paste range of cells to first empty row of active list in another worksheet

Hello all,

Have been trying codes found on this forum for two days, but nothing seems to work yet.

I am using 2003 Excel, and have a workbook with several worksheets. One of them - "Log" - is a list of 5,000 patient samples in our storage. Columns are C through N, and the entire range (C2:N5120) is an active list to enable AutoFilter. Another sheet - "New" - is a small table to simplify entering new data into the log. Headers are identical. A user enters samples one at a time, presses a button, and the macro codes for copy/pasting the values of those 12 cells into the full sample log worksheet.

The task: I need to copy a range of cells - B10:M10 - from Sheets("New") to first empty row in Sheets("Log").

Bonus: if a macro button is accidentally pressed twice, is it possible that identical information simply overwrites itself, instead of creating a duplicate entry?

The most recent code (which I think I found somewhere on this forum) is below. It does almost everything that I want, except that it pastes my line on row 29,999 (which is probably the first empty row below the activated list).

Please and thank you,
- Jane.

Please Login or Register  to view this content.

The code is pasting in to next empty row in sheet log? Is not what you want?

The first top part of the code could be shortened in to 2 lines

Please Login or Register  to view this content.

Originally Posted by AB33

The code is pasting in to next empty row in sheet log? Is not what you want?

Because it is pasting into an active list, the pasting happens below the active list, so instead of going into the very next empty row, immediately below all other entries, the macro pastes the line some twenty-five thousand lines lower.

If I shorten active list to 5120 entries (which is what I have now), the line pastes right below, but then is not included into AutoFilter of active list.

Originally Posted by AB33

The first top part of the code could be shortened in to 2 lines

Please Login or Register  to view this content.

Thank you very much! I changed the code and, on a whim, shortened the active list, and somehow it now pastes inside that list. This works!

Now, is there a way to make the duplicate overwrite itself?

Again, thank you!

If by a list you mean you have a table in sheet log, this should work.

Please Login or Register  to view this content.

Originally Posted by AB33

If by a list you mean you have a table in sheet log, this should work.

Please Login or Register  to view this content.

Excel did not like that line, and gave me 438 Debug error on that line. That said, your previous suggestion seems to have worked well, so thank you very much!

Sorry! My mix-up.

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

Excel didn't like that that, either.

But thank you so much for your help!

As long as FirstBlankCell is declared as long and sheet log is NOT complete blank, it should work.
What error are you getting?

Originally Posted by AB33

As long as FirstBlankCell is declared as long and sheet log is NOT complete blank, it should work.

Ah. I had FirstBlankCell defined as range, not as long. Fixed that, and it's working well! Before, it gave me a debug error. Thank you!