How can I get excel to label sheets automatically? eg: dates.

A

Adrian

I want Excel to label my sheets automatically, because I make a workbook
every month and I don't feel like entering every date on every tab. Is this
possible?
 
D

DCSwearingen

I have a very similar situation. I have a new workbook for every month
and a worksheet in that workbook for every day of the month.

My solution is not very elegant:
I created a workbook,

Deleted all but one sheet,

Did the formatting I want for every day in that first sheet. Headers,
number formats, etc.

Copied the sheet 30 more times

Numbered each sheet 01, 02, ... , 30, & 31

Then save the sheet as an original or as a template. Template is best
so you don't screw up you orignal as easily.

Then just use that one to copy for each month.

A simple macro could also be created to insert and name new sheets in
consecutive order if the formatting & headers are not as critical.
 
D

Dave Peterson

Start a new workbook
Add this code to a general module in that workbook's project

Option Explicit
Sub testme()
Dim iCtr As Long
Dim NewWkbk As Workbook
Dim HowMany As Long
Dim StartDate As Date

StartDate = DateSerial(Year(Date), Month(Date) + 1, 1)
StartDate = Application.InputBox(prompt:="Enter a date in the month you
want", _
Type:=1, Default:=Format(StartDate, "mmmm dd, yyyy"))

If Year(StartDate) < 2005 _
Or Year(StartDate) > 2010 Then
Exit Sub
End If

StartDate = DateSerial(Year(StartDate), Month(StartDate), 1) 'First of the
month!
HowMany = Day(DateSerial(Year(StartDate), Month(StartDate) + 1, 0))

Set NewWkbk = Workbooks.Add(1) 'single sheet
NewWkbk.Sheets.Add Count:=HowMany - 1

For iCtr = 1 To HowMany
NewWkbk.Worksheets(iCtr).Name _
= Format(StartDate - 1 + iCtr, "yyyy_mm_dd")
Next iCtr

End Sub

Back to excel
put a giant button from the Forms toolbar on the first worksheet in that new
workbook. Assign this macro to the button.

Save this workbook.

Open the workbook when you want to create a new workbook for the month and click
the giant button.
 
D

Dave Peterson

No real change--just fixing line wrapping!

Option Explicit
Sub testme()
Dim iCtr As Long
Dim NewWkbk As Workbook
Dim HowMany As Long
Dim StartDate As Date

StartDate = DateSerial(Year(Date), Month(Date) + 1, 1)
StartDate = Application.InputBox _
(prompt:="Enter a date in the month you want", _
Type:=1, Default:=Format(StartDate, "mmmm dd, yyyy"))

If Year(StartDate) < 2005 _
Or Year(StartDate) > 2010 Then
Exit Sub
End If
'First of the month!
StartDate = DateSerial(Year(StartDate), Month(StartDate), 1)
HowMany = Day(DateSerial(Year(StartDate), Month(StartDate) + 1, 0))

Set NewWkbk = Workbooks.Add(1) 'single sheet
NewWkbk.Sheets.Add Count:=HowMany - 1

For iCtr = 1 To HowMany
NewWkbk.Worksheets(iCtr).Name _
= Format(StartDate - 1 + iCtr, "yyyy_mm_dd")
Next iCtr

End Sub
 
A

Adrian

Thank you very much!!!!!!

Dave Peterson said:
Start a new workbook
Add this code to a general module in that workbook's project

Option Explicit
Sub testme()
Dim iCtr As Long
Dim NewWkbk As Workbook
Dim HowMany As Long
Dim StartDate As Date

StartDate = DateSerial(Year(Date), Month(Date) + 1, 1)
StartDate = Application.InputBox(prompt:="Enter a date in the month you
want", _
Type:=1, Default:=Format(StartDate, "mmmm dd, yyyy"))

If Year(StartDate) < 2005 _
Or Year(StartDate) > 2010 Then
Exit Sub
End If

StartDate = DateSerial(Year(StartDate), Month(StartDate), 1) 'First of the
month!
HowMany = Day(DateSerial(Year(StartDate), Month(StartDate) + 1, 0))

Set NewWkbk = Workbooks.Add(1) 'single sheet
NewWkbk.Sheets.Add Count:=HowMany - 1

For iCtr = 1 To HowMany
NewWkbk.Worksheets(iCtr).Name _
= Format(StartDate - 1 + iCtr, "yyyy_mm_dd")
Next iCtr

End Sub

Back to excel
put a giant button from the Forms toolbar on the first worksheet in that new
workbook. Assign this macro to the button.

Save this workbook.

Open the workbook when you want to create a new workbook for the month and click
the giant button.
Click to expand...
 
D

DCSwearingen

Many Thanks to Dave Peterson!!

In my earlier, not so elegant way of doing this, I wanted to maintain
my report formatting.

I copied that formatting to the the worksheet with the macro button and
saved my workbook with this macro as "Add_CR_Workbook.xls" and added the
following to the end of Dave code:

Windows("Add_CR_Workbook.xls").Activate
Cells.Copy

Windows(NewWkBkName).Activate
For iCtr = 1 To Worksheets.Count
Sheets(iCtr).Select
Cells.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next iCtr
 
D

Dave Peterson

Glad you got your variation working.

Another way to do this would be to do that formatting to the first worksheet.
Then copy that first worksheet lots of times (instead of inserting a new
"generic" worksheet).
 
Top