format cells in 30 different sheets
 

Hi,
I have a workbook containing 31 sheets (one for each day) named from 1 to
31. In a cell I want to display the date as "1-May-07", next sheet
"2-May-07",...
For now I have set up a custom format like 0"-May-07" so I only enter the
numbers 1 to 31. Now, if i create the same workbook for another month, is
there a quick way to turn "May" into "June" without changing the first cell
format and copy it into each cell in each sheet?

Thank you !

format cells in 30 different sheets
 

That's an odd way of doing it! I would format the cells as date and
arrange to have each cell except for the first one to increment the
cell from the previous sheet, i.e. a formula like:

=Sheet1!A1 + 1 in Sheet2
=Sheet2!A1 + 1 in Sheet3
=Sheet3!A1 + 1 in Sheet4, etc

Then all you need to do is enter 1-Jun-07 in A1 on the first sheet and
the other sheets will have the date rippled through.

Hope this helps.

Pete


On Mar 15, 2:55 pm, Pat wrote:
Hi,
I have a workbook containing 31 sheets (one for each day) named from 1 to
31. In a cell I want to display the date as "1-May-07", next sheet
"2-May-07",...
For now I have set up a custom format like 0"-May-07" so I only enter the
numbers 1 to 31. Now, if i create the same workbook for another month, is
there a quick way to turn "May" into "June" without changing the first cell
format and copy it into each cell in each sheet?

Thank you !

format cells in 30 different sheets
 

Hi,

Copy your file as another name for example the month you want to setup, open
new file, active the sheet named 1, hold the Ctrl key and click on other
sheets tab, release the Ctrl key active the cell that you formated and then
change May to June.
the change should be effected in all sheet in the specific cell.

Thanks,

--
Farhad Hodjat


"Pat" wrote:

Hi,
I have a workbook containing 31 sheets (one for each day) named from 1 to
31. In a cell I want to display the date as "1-May-07", next sheet
"2-May-07",...
For now I have set up a custom format like 0"-May-07" so I only enter the
numbers 1 to 31. Now, if i create the same workbook for another month, is
there a quick way to turn "May" into "June" without changing the first cell
format and copy it into each cell in each sheet?

Thank you !

format cells in 30 different sheets
 

Pat

Sub Date_Increment()
''increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2007, 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

Just change the DateSerial to (2007, 6, 1)

Note: since you have 31 sheets, there is no June 31st so the last sheet will get
July 1st in A1


Gord Dibben MS Excel MVP


On Thu, 15 Mar 2007 07:55:10 -0700, Pat wrote:

Hi,
I have a workbook containing 31 sheets (one for each day) named from 1 to
31. In a cell I want to display the date as "1-May-07", next sheet
"2-May-07",...
For now I have set up a custom format like 0"-May-07" so I only enter the
numbers 1 to 31. Now, if i create the same workbook for another month, is
there a quick way to turn "May" into "June" without changing the first cell
format and copy it into each cell in each sheet?

Thank you !