find and copy last updated row to worksheet

[everscern]

is there any macro which finds the last updated row and copys the selected row to a worksheet (let's say AAA) ?

 

[tmcfadden]

What do you mean by "last updated row"?

How and when is the worksheet updated?

How is the data laid out?

-Tim

 

[everscern]

picture 1

picture 2

the highlighted rows are the last updated rows. Upon clicking a button, I hope the last updated row will be transferred to a worksheet.

 

[tmcfadden]

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:

Sub CopyLastUpdatedRow()

ActiveSheet.Rows(Sheets("Vars").Range("LastUpdatedRow")).EntireRow.Copy

End Sub

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

End Sub

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()
CopyLastUpdatedRow
End Sub

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 ).

-Tim

 

[everscern]

hi.. I tried using this macro. but i do not know how to get the summarized code ' copylastupdatedrow'. Could u tell me how?

Private Sub CommandButton1_Click() 
CopyLastUpdatedRow 
End Sub

 

[tmcfadden]

Sorry, I've been out of the office for a while.

Do run that macro, you have to put the routine "CopyLastUpdatedRow" someplace that's visible to the button.

My suggestion would be to add it into a module:

• Open the worksheet
  Open the VBA Editor ( Tools -> Macro -> VBA Editor )
  Insert a new module ( Insert -> Module )
  Paste the code I gave you into this window

-Tim