Naming Worksheets from a list in a sheet

D

dr.dodge

Help and guidance required, please.

I have a Workbook that includes one sheet for each week of the year. I
would like to name each sheet with the weekend date for each week (something
like 02 Jan, 09 Jan etc). It's a real pain to have to manually do this for
each workbook I create.
Can I list all the dates in one of the sheets and then use that list to
rename the other sheets?

I then need to refer to each sheet to gather summary information to another
sheet in the same workbook. (e.g. Sheet1!A1 +Sheet2!a1 etc)

Is there an easier way to name the sheets?
How do I then refer to the sheets to extract data to a summary?

God this reads like gibberish. Hope it makes sense!!

Any help appreciated.

dr dodge
 
B

Bob Phillips

To name the sheets, use this simple VBA code

Sub NameSheets()
Dim shDate As Date
Dim sh As Worksheet

shDate = DateSerial(2004, 1, 2)
For Each sh In ActiveWorkbook.Worksheets
sh.Name = Format(shDate, "dd mmm yyyy")
shDate = shDate + 7
Next sh

End Sub

To reference them in a sum, use

=SUM('02 Jan 2004:31 Dec 2004'!A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

dr.dodge

This is stunning. Many thanks

dr dodge


Bob Phillips said:
To name the sheets, use this simple VBA code

Sub NameSheets()
Dim shDate As Date
Dim sh As Worksheet

shDate = DateSerial(2004, 1, 2)
For Each sh In ActiveWorkbook.Worksheets
sh.Name = Format(shDate, "dd mmm yyyy")
shDate = shDate + 7
Next sh

End Sub

To reference them in a sum, use

=SUM('02 Jan 2004:31 Dec 2004'!A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top