Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modify dates byr referencing a cell name
Here is the situation:
I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18 etc. I have a cell in each sheet that references the beginning date of each sheet. Ie May 5; May 12 etc As I am preparing 52 sheets I would prefer not to have to fill in the date in each cell, in each sheet, or formulate by referencing the end date in the previos sheet and adding one. I would like to have a macro do it, but haven't much success so far. Of course I would have done manually by now for the time taken to figure this out. :( -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modify dates byr referencing a cell name
Robin,
Sub TryNow() Dim myS As Worksheet Dim myDate As Date myDate = DateValue("5/5/2008") For Each myS In Worksheets myS.Range("A1").Value = myDate myDate = myDate + 7 Next myS End Sub Of course, change the Starting date and the cell in which you write the date. Note that what you are doing is typical of poor workbook design. Much better would be to use a database, with a column of dates along with the other values. Then you could slice and dice your data using Pivot Tables and filters, without having 52 sheets...... HTH, Bernie MS Excel MVP "Robin" wrote in message ... Here is the situation: I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18 etc. I have a cell in each sheet that references the beginning date of each sheet. Ie May 5; May 12 etc As I am preparing 52 sheets I would prefer not to have to fill in the date in each cell, in each sheet, or formulate by referencing the end date in the previos sheet and adding one. I would like to have a macro do it, but haven't much success so far. Of course I would have done manually by now for the time taken to figure this out. :( -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modify dates byr referencing a cell name
Thanks! I only wish I knew databases!
On Wed, 30 Apr 2008 11:42:40 -0600, Bernie Deitrick <deitbe consumer dot org wrote: Robin, Sub TryNow() Dim myS As Worksheet Dim myDate As Date myDate = DateValue("5/5/2008") For Each myS In Worksheets myS.Range("A1").Value = myDate myDate = myDate + 7 Next myS End Sub Of course, change the Starting date and the cell in which you write the date. Note that what you are doing is typical of poor workbook design. Much better would be to use a database, with a column of dates along with the other values. Then you could slice and dice your data using Pivot Tables and filters, without having 52 sheets...... HTH, Bernie MS Excel MVP "Robin" wrote in message ... Here is the situation: I have 52 sheets each named by week, ie: May 5-May 11; May 12-May 18 etc. I have a cell in each sheet that references the beginning date of each sheet. Ie May 5; May 12 etc As I am preparing 52 sheets I would prefer not to have to fill in the date in each cell, in each sheet, or formulate by referencing the end date in the previos sheet and adding one. I would like to have a macro do it, but haven't much success so far. Of course I would have done manually by now for the time taken to figure this out. :( -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modify dates byr referencing a cell name
Thanks! I only wish I knew databases!
Well, here's a good opportunity to give it a try... Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing dates | Excel Discussion (Misc queries) | |||
Modify a cell | Excel Discussion (Misc queries) | |||
Modify cell contents | Excel Discussion (Misc queries) | |||
Modify Cell Sizes | Excel Worksheet Functions | |||
Modify Cell sizes | Excel Worksheet Functions |