Microsoft Excel

Hi Folks,

I've got a bit of a problem which I hope you guys can help with. This may get a bit complicated, so apologies in advance

I've working on a little report spreadsheet, and want to do the following: I want to automatically copy data from one spreadsheet to the other, then delete the original data. Now, so far, I've managed to write a little formula to move the data over from one sheet to the other, but don't know how to make it delete the original data.

Does anyone know if this is possible, and if so, how do I do it?

Cheers

Rockape

Hi Rockape,

I think you can do it with Macros, just record the tasks (cut/paste) and you are done.
Now you have to schedule this macro to run every x minutes.
Hope this helps.

Cheers,

Hi Alans,

Thanks for that, I thought I might have to go down the Macro route. The problem is, what I'm trying to do, is make the changes when a state change occurs on the first page. So, for example a state changes from "on going" to "closed". When the state changes to "closed" all the relevant data would get copied to another sheet, then the original data is deleted. So, do you think that could be done with a Macro?

I'm not sure if you can run it exaclty when the state changes, but you can schedules to check certain cells for certain values in the worksheet each x min.
For example, below macro will check cell B5 for a value, if that value is "closed" then it will "cut" it and paste it into cell B8 otherwise a message box shows message "Done" (you can remove message box and not doing any action).
[code:1]
Sub Macro1()
'
' Macro1 Macro
'

'
If Range("B5") = "closed" Then

Selection.Cut
Range("B8").Select
ActiveSheet.Paste

Else: MsgBox "done"

End If


End Sub
[/code:1]

Check this link for more info regarding these VB codes
www.angelfire.com/biz7/julian_s/julian/julians_macros.htm

Cheers,

Alans,

Thanks for that, I'll have a play with that later. Oh, and by the way, what a fantastic website in the link thanks for that.

Cheers

Rockape