I don't know if this went through to post the first time so am trying it
again.
Debbie
Everything leads to more questions. You say you have a bunch of data over a
number of rows. It is all grouped by the values in Column B (right now I
think that is immaterial).
The user inserts a new blank row somewhere.
The user then fills-in some data in that new row.
You want to extract some/all of the data in that row after he gets through
entering data.
The first question that has to be resolved regards how you want to initiate
this data extraction/copy.
I gather, although you didn't say, that the destination for this data is not
constant. That means that the copy/paste has to be done by macro.
How to trigger this macro to execute? Here's two ways:
1. The user clicks on a button, as instructed by you. OR
2. The macro automatically executes upon the occurrence of
some event. For instance: The user enters data into a specific column (in
that row). If you elect to go this route, the following question regarding
how to find the row number is mute. The row number would always be the row
of the cell just filled.
The next big question is how does Excel know the row number of the blank row
the user inserted?
Excel does not recognize the insertion of a new row as an event.
But Excel does recognize a change in cell content as an event.
A row insertion (say row 5) causes the contents of all the cells in that row
to change. That is, the occupied cells of the previous row 5 are now blank
in the new row 5, hence the contents of row 5 changed.
You can use this change to trigger a macro to store the row number somewhere
(out of the way).
Note that this macro would be triggered to execute with any change to any
cell on the sheet for whatever reason. You would have to place code in the
macro to distinguish between a change to a cell (do nothing) and the
insertion of a row (store the row number).
When the macro executes (by one of the methods above) it will
look up the stored row number and do what you want.
Note that if the user elects to input his data in the first
blank row below your data, he would not insert a row, a cell content change
due to the row insertion will not occur, and the row number would not be
stored. You can force the user to insert a row in this case by simply
typing something innocuous (like the company name, etc) in the row below
your data.
These are just comments and suggestions about how you could
solve your problem. Let me know if I can be of help with the specifics.
HTH Otto
