1st of the month cell event--How to

[KWFIELDS969]

I am using excel to manage my spare parts inventory. I would like to copy one column to another on the fist of each month and then reset two columns to zero after the copy event. I have 4 columns that need work, 1. Monthly start qty 2. qty in stock 3. parts received 4. parts used. I need to have the numbers from the QTY in stock column to be copied to the Monthly start qty column on the first of every month. Then I need to reset the two remaining columns to a value of zero after the move is completed. [TABLE="width: 500"]
<tbody>[TR]
[TD]monthly start[/TD]
[TD]parts received[/TD]
[TD]parts used[/TD]
[TD]qty in stock[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]399[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[venkat1926]

no blank rows, columns,cells. instead of blank enter 0

copy sheeet 1 to sheet 2 to preserve data for some time

now you have to run this macro

Sub test()
Dim r As Range, c As Range
Worksheets("sheet1").Activate
Set r = Range(Range("A2"), Range("A2").End(xlDown))
If Day(Date) = 1 Then
Range(Range("D2"), Range("d2").End(xlDown)).Copy Range("A2")
Range(Range("B2"), Range("D2").End(xlDown)).Cells.Clear
End If






End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub

today may not be the first so nothing will happen. if you want test modify
if day(date)=<todays date no. eg. 26> then
and then run the macro

now run undo macro
and change the above code to
If Day(Date) = 1 Then