Date Activated Macro

woodier · Mar 31st, 2002, 09:49 PM

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 · Apr 1st, 2002, 12:24 AM

Hi Woodier

How about

Code:

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 · Apr 1st, 2002, 10:09 PM

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

Dave Hawley · Apr 1st, 2002, 10:12 PM

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 · Apr 1st, 2002, 10:18 PM

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 · Apr 1st, 2002, 10:29 PM

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 · Apr 2nd, 2002, 01:53 PM

Dave,

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

My sincere thanks.

Apr 1st, 2002, 12:24 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Woodier How about Code: 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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 31st, 2002, 09:49 PM	#1
woodier New Member Join Date: Feb 2002 Posts: 8	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.

Apr 1st, 2002, 10:09 PM	#3
woodier New Member Join Date: Feb 2002 Posts: 8	Sorry Dave,but your Macro did not produce any results.

Apr 1st, 2002, 10:12 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 ]

Apr 1st, 2002, 10:18 PM	#5
woodier New Member Join Date: Feb 2002 Posts: 8	Dave, I attempted to run your Macro on April 1. I'll try it again, with your suggested name change, on May 1.

Apr 1st, 2002, 10:29 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Apr 2nd, 2002, 01:53 PM	#7
woodier New Member Join Date: Feb 2002 Posts: 8	Dave, The Macro works, after I changed the names of the Sheets. My sincere thanks.