vba macro

[Bob Clausen]

I have a workbook with 31 worksheets.
One worksheet for everyday of the month.
They are named 1,2,3,...29,30,31
I am having trouble writing a macro to do the following.
In Range("k16") is a value on each worksheet.
On the worksheet #2 i want to average the value of k16 of the first and second sheet.
On the worksheet #3 i want to average the value of k16 of the first, second, third worksheet. I hope you follow my logic.
Of course I don't want to average all worksheets until that day has come to the corresponding worksheet.
I want to put the average for day 1 & 2 on the second worksheet in let's say Range("k17").
Getting caught up in opening sheets or correct order.
Thankyou, Bob

 

[nightcrawler23]

Sub daysAvg()
Dim currDay As Integer
currDay = Day(Date)
For i = 2 To currDay
    Sheets("" & i).Select
    Sheets("" & i).Range("K17") = (Sheets("" & (i - 1)).Range("K16") * (i - 1) + Sheets("" & i).Range("K16")) / i
Next i
End Sub

Try This

 

[nightcrawler23]

BTW,
Welcome to the Board.

 

[Bob Clausen]

Thanks so much, I will try it. I didn't try doing sheets("" & i) to call up sheet.
Bob

 

[wolverineb]

This is also possible using only formulas, although it's ugly.

Basically, you can use the fact that this formula gives you the name of the current worksheet as a number:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)*1

And this one tells you what's in the previous sheet's cell K17:

=INDIRECT(((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)*1)-1)&"!K17")

Oh, and this gives you the number of the current day:

=DAY(TODAY())

Combine things in the same way nightcrawler did in his code and voila! Easy as pie.