This is a bit tricky because you have to keep track of the last row that was updated. To do this ( since you can't have global variables ), I created a new worksheet named "Vars". On that worksheet, I have a named range called "LastUpdatedRow".
The code that does the actual copying ( in your case, you might want to do other things with it ) is located in a module. Here's that code:
Now we just have to keep that range updated. I put some code in Sheet1's 'Change' event to do just that:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Vars").Range("LastUpdatedRow").Value = Target.Row
The above code should go into any worksheet that you want to enable this functionality.
Now that we're keeping the value current, I added a command button that would call the function I created ( CopyLastUpdatedRow ):
Private Sub CommandButton1_Click()
That's it. I can now change the worksheet and hit the command button. The command button calls my specialized function ( which you'll have to change ).