How do I Copy certain data to another sheet-automatically

[scrumpyjack]

OK so I have this piece of code that will, when a command button is pushed trawl thru my sheet and copy and paste all completed actions to another sheet. But...it'll only work when the button is pushed and when the button is pushed some days later the same info is copied and pasted along with new info.
So can someone add to my bit of code another bit of code that will clear the 'Completed cases' page prior to pasting the new data and a piece of code that will do all this when a new completed case is inputted in into 'Cases by case number' page please.
Thanks in advance
As below

Please Login or Register  to view this content.

[AlphaFrog]

This will copy just the one row when the user sets column R to "completed". It pastes the one row to the bottom of the data on the Completed cases worksheet.

Put this in the same location as your CommandButton1_Click procedure. You could delete that procedure (and the button) if this works for you.

Please Login or Register  to view this content.

[rcm]

this will be activated when a cell value is changed but executed only when a cell in column 18 is changed

Please Login or Register  to view this content.

[scrumpyjack]

Alpha Frog..thanks mate works a treat.
Hope the weather in Florida is better than the rubbish here!!
Fred

[scrumpyjack]

Alpha Frog is there a PartRow command I can use to only copy certain cells from the completed case row? ie I only need certain data from the case to be in the completed case sheet.
Thanks Fred

[AlphaFrog]

Alpha Frog is there a PartRow command I can use to only copy certain cells from the completed case row? ie I only need certain data from the case to be in the completed case sheet.
Thanks Fred

There are different methods to define part of a row depending on your requirements. This is one example to copy from A to G based on the Target cell which in your case is R

Range("A" & Target.Row & ":G" & Target.Row).Copy _

[scrumpyjack]

Cheers AlphaFrog
Understand that. Works a treat but...
What if I just wanted cells A, J and K for example?

[AlphaFrog]

Cheers AlphaFrog
Understand that. Works a treat but...
What if I just wanted cells A, J and K for example?

Intersect(Range("A:A,J:K"), Target.EntireRow).Copy _