Date Activated Macro

[woodier]

On the 1st day or each month, it will take the last entry in column H, add a fixed amount of currency to it and insert the sum into the 1st empty cell in column H. Within the same row, it would insert the current date into column B, and insert a zero into column C.

Any assistance will be greatly appreciated.

 

[Dave Hawley]

Hi Woodier

How about

Private Sub Workbook_Open()

If Month(DateAdd("d", -1, Date))<> Month(Date) Then

    With Sheet1.Range("H65536").End(xlUp)
       .Value = Sheet1.Range("H65536").End(xlUp) + 25.3
       .Offset(0, 6) = Date
       .Offset(0, 5) = 0
    End With
 
End If
 
End Sub

Placed in the Private Module of ThisWorkbook.



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-01 00:31

 

[woodier]

Sorry Dave,but your Macro did not produce any results.

 

[Dave Hawley]

Works fine when I run it! Don't forget it is set to only run on the first day of each month. You may also need to change the CodeName ("Sheet1") to suit.

The Offsets should also be negatives. IE
.Offset(0, -6) = Date
.Offset(0, -5) = 0


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-01 21:14

 

[woodier]

Dave,

I attempted to run your Macro on April 1. I'll try it again, with your suggested name change, on May 1.

 

[Dave Hawley]

woodier, no need to wait that long! Just change the:

Month(DateAdd("d", -1, Date)) <> Month(Date)

To

Month(DateAdd("d", -2, Date)) <> Month(Date)


This will make the Month of today's date <> to the date of Today -2, If the date there is the second of the Month.

 

[woodier]

Dave,

The Macro works, after I changed the names of the Sheets.

My sincere thanks.