Prev: When I search on my name "Meenie" I don't get 2010 posts
Next: Combine an And OR function
From: Ann on 29 Apr 2010 10:59
I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
How can I type in the first day of the month and have each successive sheet
advance one day without typing it on each sheet individually?
Thanks so much.
--
Ann
From: Luke M on 29 Apr 2010 11:09
You could use this short macro to do all the work. Note that you can't
actually name a sheet "5/1/2010 Sat" as slash marks are not allowed in sheet
names.


Sub DateSheets()
'Starting Value
x = DateValue("5/1/2010")
i = 1
'First, clear out the old names
For Each ws In ThisWorkbook.Worksheets
ws.Name = "zzzz" & i
i = i + 1
Next

'Now, give it the real name
For Each ws In ThisWorkbook.Worksheets
'Note that you can't use a / in sheet names!
ws.Name = Format(x, "dd.mm.yyyy ddd")
x = x + 1
Next ws

End Sub


--
Best Regards,

Luke M
"Ann" <Ann(a)discussions.microsoft.com> wrote in message
news:9D2056EE-936C-4123-964D-BAFF84B2243A(a)microsoft.com...
>I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
> How can I type in the first day of the month and have each successive
> sheet
> advance one day without typing it on each sheet individually?
> Thanks so much.
> --
> Ann


From: Jacob Skaria on 29 Apr 2010 11:08
Try the below

--Make sure you try this in a saved workbook
--Name the sheets as 1,2,3,4...upto 31
--Enter the first date say 05/01/2010 in Sheet '1' cell A1
--Select sheets 1 to 31 and select cell A1 and format it to the date format
you need.
--Select sheets 2 to 31 and enter the below formula in cell A1
PS: You can select or group the sheets by holding the control key and
selecting the sheets..



=INDIRECT("'" & REPLACE(CELL("Filename",A1),1,FIND("]",
CELL("filename",A1)),"")-1 & "'!A1")+1

--
Jacob (MVP - Excel)


"Ann" wrote:

> I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
> How can I type in the first day of the month and have each successive sheet
> advance one day without typing it on each sheet individually?
> Thanks so much.
> --
> Ann
From: Luke M on 29 Apr 2010 11:14
I realize now that Jacob and I interpretted your request differntly. I was
thinking you wanted to name the sheets, Jacob's solution is for putting the
date IN the sheet (which, in rereading, looks to be what you are really
wanting).

--
Best Regards,

Luke M
"Luke M" <lukemoraga(a)nospam.com> wrote in message
news:uuVS0365KHA.980(a)TK2MSFTNGP04.phx.gbl...
> You could use this short macro to do all the work. Note that you can't
> actually name a sheet "5/1/2010 Sat" as slash marks are not allowed in
> sheet names.
>
>
> Sub DateSheets()
> 'Starting Value
> x = DateValue("5/1/2010")
> i = 1
> 'First, clear out the old names
> For Each ws In ThisWorkbook.Worksheets
> ws.Name = "zzzz" & i
> i = i + 1
> Next
>
> 'Now, give it the real name
> For Each ws In ThisWorkbook.Worksheets
> 'Note that you can't use a / in sheet names!
> ws.Name = Format(x, "dd.mm.yyyy ddd")
> x = x + 1
> Next ws
>
> End Sub
>
>
> --
> Best Regards,
>
> Luke M
> "Ann" <Ann(a)discussions.microsoft.com> wrote in message
> news:9D2056EE-936C-4123-964D-BAFF84B2243A(a)microsoft.com...
>>I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
>> How can I type in the first day of the month and have each successive
>> sheet
>> advance one day without typing it on each sheet individually?
>> Thanks so much.
>> --
>> Ann
>
>


From: Gord Dibben on 29 Apr 2010 11:20
Sub Date_Increment()
''increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2010, 5, 1)
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

On Thu, 29 Apr 2010 07:59:02 -0700, Ann <Ann(a)discussions.microsoft.com>
wrote:

>I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
>How can I type in the first day of the month and have each successive sheet
>advance one day without typing it on each sheet individually?
>Thanks so much.

 |  Next  |  Last
Pages: 1 2
Prev: When I search on my name "Meenie" I don't get 2010 posts
Next: Combine an And OR function