Naming Worksheets from a list in a sheet

  • Thread starter Thread starter dr.dodge
  • Start date Start date
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
 
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)
 
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...
 
Back
Top