How do I make my macro update each month?
 

I have a spreadsheet that is updated monthly.

There is one column per month.

How do I create a macro that will insert the numbers in the correct column.

For example, once the September is filled in, I now want to fill in the
October column.

Any suggestions?

How do I make my macro update each month?
 

Create generic function that act based on a named range
then in the new column you name the range you process with the current month

in the generic functions change the last month with the new month.


Dissecting:



you have a certain function that processs data in range "A:A",


this is your function


process_month(month as range) as integer


then you pass the "month" argument of the type range to be processed by the
function


in your sheet .


select column "A:A" , name to "January"


then column "B:B" will be "february",


column "C:C" would be "March".. and so on.



then in you formula you would pass the argument as follows

my_var = process_month("january")

when the month changes, you only have to replace the "month" argument in
your formula


it is also more flexible if you store the name of the current month in
somewhere into your sheet.

example:


instead of replacing the "month" argument in your code you store the
current month name into a cell outside from the limits of your data , like
AK100 or so



then your generic formula would be like this



my_var = process_month(activesheet.range("ak100").value)





--
---
zz [MX]
cuasi-musico,semi-poeta y loco


"MacroLearning" wrote in message
...
I have a spreadsheet that is updated monthly.

There is one column per month.

How do I create a macro that will insert the numbers in the correct
column.

For example, once the September is filled in, I now want to fill in the
October column.

Any suggestions?

How do I make my macro update each month?
 

Assuming you have the month names in row 1 in columns 1 to 12 then
Sub BuildCalendar()
Dim dt As Date
Dim cell As Range
Dim last As Long, i As Long
Range("A2").Resize(31, 12).ClearContents
For Each cell In Range("A1:L1")
dt = DateValue(cell.Value & " 1, " & Year(Date))
last = Day(DateSerial(Year(dt), Month(dt) + 1, 0))
For i = 1 To last
cell.Offset(i, 0) = i
Next
Next
End Sub

If that isn't exactly what you want, maybe you can see the pieces you need
from within it.

--
Regards,
Tom Ogilvy


"MacroLearning" wrote:

I have a spreadsheet that is updated monthly.

There is one column per month.

How do I create a macro that will insert the numbers in the correct column.

For example, once the September is filled in, I now want to fill in the
October column.

Any suggestions?