auto date changing on worksheets

[never]

I have worksheets for the week days in the same workbook that I have to date
everyday. example: 7/28/08, 7/29/08 etc. the format does not matter but I
would like it to auto date instead of entering dates manually each day. Maybe
if I enter the first date and then it auto adds the other dates for the
remainder of the sheets?
Any help is appreciated. Thanks.

 

[Roger Govier]

Hi

In cell A1 enter your starting date
in A2
=A1+1
Copy down as far as required.

Changing the value in A1, will change all subsequent values

 

[never]

Thanks, but the dates are on different worksheets, not the same one. Monday's
date on worksheet tab 1, Tues on worksheet tab 2, Wed on tab 3 etc... I would
like to have them self date without having to put date on each sheet
manually. For instance you can add a person's name by grouping the sheets, so
you only have to type it in once for the whole week. Right now I am typing in
dates manually, if I use a TODAY() function then it changes each time I open
the worksheet and the worksheets will be opened multiple times on any given
date in the future so this is not a useful function for what I would like to
use. Thanks again for any other ideas.

 

[Gord Dibben]

Run this macro whenever you want to change the incremented dates across
sheets.

Sub Date_Increment()
Dim myDate As Date
Dim iCtr As Long
myDate = InputBox("Enter a date")
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm-dd-yyyy"
End With
Next iCtr
End Sub


Gord Dibben MS Excel MVP

 

[never]

Thanks, but I am techie challenged. I don't know what any of that means or
how/where to input that info. I do know how to run a macros but I'm not
familiar with Dim & iCtr and so forth. Can you help me a little more, please?
Thanks!

 

[Gord Dibben]

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

As far as "Dim" and "iCtr"..............

iCtr is an arbitrary name for a variable.............and Dim tells Excel
what type of variable and what storage space to allocate for that value.

In this case ICtr is the number returned from Worksheets.Count


Gord

 

[Roger Govier]

Hi

You could download a file I created which allows you to set up a series of
workbooks for each month of the year, with a separate sheet for each day.
You will find the file at Excelusergroup.org

http://excelusergroup.org/media/p/236.aspx

Maybe it will help you, or give you some ideas to modify to suit your own
needs.

 

[never]

I tried to create the general module and pasted the code but when I tried to
run it the syntax box opened and said something is not right. And since I
don't understand code speak I cannot figure it out. Thanks for your help.

 

[never]

The certificate for your download is not signed and as this is for work I am
not allowed to download without an authorized certficate. But thanks, anyway.