Dates incremented across sheets

[bwoolley]

I have a workbook with 84 sheets, one for each day. I cannot figure out how to have excel automatically increment the date in the same cell on each sheet. For example, on day one(sheet 1) I have entered the start date of 3/18/02 into C5. I am trying to figure out how to get day 2 C5 to read 3/19/02.... day 84 to read 6/10/02 with out having to enter them all in manually. Any suggestions?

 

[nisht]

Try with this code..

i have not checked this code.. as i have no 84 sheets...lol

Sub dayincrement()
Dim sht As Worksheet
Dim i As Integer
For Each sht In Worksheets
[c2] = Format([c2], "dd/mm/yyyy")
[c2] = [c2] + i
i = 1 + i
Next sht

End Sub


ni****h desai
http://www.pexcel.com

 

[bwoolley]

ok, I'm a novice.... where do I enter that code?

 

[bwoolley]

**bump**

can anyone help me here?

 

[Chris Davison]

are all your sheets called :

sheet 1
sheet 2
sheet 3
sheet 4

etc etc ?

if they are, we can use this 1,2,3,4,etc to our advantage....

or was that just an example ?



edit... I'm off to bed soon... if anyone sees this post maybe they can use the match replace thing in the filename to generate a number with which to increment the date by if the above question is "yes"
This message was edited by Chris Davison on 2002-04-16 14:18

 

[bwoolley]

Actually they are labled day 1, day 2....

 

[nisht]

Press Alt + F11 and then slect vbaproject in left window... right click it.. and here select insert module.. now double click the module.. and you will see the code window.. paste the code there..

you can go to my site http://www.pexcel.com/download.htm and see how modules are placed.

ni****h desai

 

[nisht]

Press Alt + F11 and then slect vbaproject in left window... right click it.. and here select insert module.. now double click the module.. and you will see the code window.. paste the code there..

you can go to my site http://www.pexcel.com/download.htm and see how modules are placed.

ni****h desai

 

[bwoolley]

I get a runtime error 13 type mismatch when I try to run it. I'm thinking about just changing them all by hand, but I am curious about the macros

 

[Chris Davison]

try this in C5 in each sheet

=INDIRECT("'Day "&((RIGHT(CELL("filename",C5),2)*1)-1)&"'!c5")+1

it seems to work for me



_________________
Hope this helps,
Chris



edit........ there's some SINGLE quotes in there amongst the DOUBLE quotes... make sure you squint right up to the screen to get them (or paste it into excel as unicode text)
This message was edited by Chris Davison on 2002-04-17 14:48