software

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?

Do you need a dated tab name for every day of each month?Yes that is exactly what I need.

quot;Dave Oquot; wrote:

gt; Do you need a dated tab name for every day of each month?
gt;
gt;


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, amp; 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 amp; headers are not as critical.--
DCSwearingen------------------------------------------------------------------------
DCSwearingen's Profile: www.excelforum.com/member.php...oamp;userid=21506
View this thread: www.excelforum.com/showthread...hreadid=542592Start 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:=quot;Enter a date in the month you
wantquot;, _
Type:=1, Default:=Format(StartDate, quot;mmmm dd, yyyyquot;))

If Year(StartDate) lt; 2005 _
Or Year(StartDate) gt; 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, quot;yyyy_mm_ddquot;)
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.

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

--

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:=quot;Enter a date in the month you wantquot;, _
Type:=1, Default:=Format(StartDate, quot;mmmm dd, yyyyquot;))

If Year(StartDate) lt; 2005 _
Or Year(StartDate) gt; 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, quot;yyyy_mm_ddquot;)
Next iCtr

End SubDave Peterson wrote:
gt;
gt; Start a new workbook
gt; Add this code to a general module in that workbook's project
gt;
lt;lt;snippedgt;gt;
gt;
gt; Back to excel
gt; put a giant button from the Forms toolbar on the first worksheet in that new
gt; workbook. Assign this macro to the button.
gt;
gt; Save this workbook.
gt;
gt; Open the workbook when you want to create a new workbook for the month and click
gt; the giant button.
gt;
gt; Adrian wrote:
gt; gt;
gt; gt; I want Excel to label my sheets automatically, because I make a workbook
gt; gt; every month and I don't feel like entering every date on every tab. Is this
gt; gt; possible?
gt;
gt; --
gt;
gt; Dave Peterson

--

Dave Peterson

Thank you very much!!!!!!

quot;Dave Petersonquot; wrote:

gt; Start a new workbook
gt; Add this code to a general module in that workbook's project
gt;
gt; Option Explicit
gt; Sub testme()
gt; Dim iCtr As Long
gt; Dim NewWkbk As Workbook
gt; Dim HowMany As Long
gt; Dim StartDate As Date
gt;
gt; StartDate = DateSerial(Year(Date), Month(Date) 1, 1)
gt; StartDate = Application.InputBox(prompt:=quot;Enter a date in the month you
gt; wantquot;, _
gt; Type:=1, Default:=Format(StartDate, quot;mmmm dd, yyyyquot;))
gt;
gt; If Year(StartDate) lt; 2005 _
gt; Or Year(StartDate) gt; 2010 Then
gt; Exit Sub
gt; End If
gt;
gt; StartDate = DateSerial(Year(StartDate), Month(StartDate), 1) 'First of the
gt; month!
gt; HowMany = Day(DateSerial(Year(StartDate), Month(StartDate) 1, 0))
gt;
gt; Set NewWkbk = Workbooks.Add(1) 'single sheet
gt; NewWkbk.Sheets.Add Count:=HowMany - 1
gt;
gt; For iCtr = 1 To HowMany
gt; NewWkbk.Worksheets(iCtr).Name _
gt; = Format(StartDate - 1 iCtr, quot;yyyy_mm_ddquot;)
gt; Next iCtr
gt;
gt; End Sub
gt;
gt; Back to excel
gt; put a giant button from the Forms toolbar on the first worksheet in that new
gt; workbook. Assign this macro to the button.
gt;
gt; Save this workbook.
gt;
gt; Open the workbook when you want to create a new workbook for the month and click
gt; the giant button.
gt;
gt; Adrian wrote:
gt; gt;
gt; gt; I want Excel to label my sheets automatically, because I make a workbook
gt; gt; every month and I don't feel like entering every date on every tab. Is this
gt; gt; possible?
gt;
gt; --
gt;
gt; Dave Peterson
gt;


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 quot;Add_CR_Workbook.xlsquot; and added the
following to the end of Dave code:

Windows(quot;Add_CR_Workbook.xlsquot;).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--
DCSwearingen------------------------------------------------------------------------
DCSwearingen's Profile: www.excelforum.com/member.php...oamp;userid=21506
View this thread: www.excelforum.com/showthread...hreadid=542592Glad 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
quot;genericquot; worksheet).
DCSwearingen wrote:
gt;
gt; Many Thanks to Dave Peterson!!
gt;
gt; In my earlier, not so elegant way of doing this, I wanted to maintain
gt; my report formatting.
gt;
gt; I copied that formatting to the the worksheet with the macro button and
gt; saved my workbook with this macro as quot;Add_CR_Workbook.xlsquot; and added the
gt; following to the end of Dave code:
gt;
gt; Windows(quot;Add_CR_Workbook.xlsquot;).Activate
gt; Cells.Copy
gt;
gt; Windows(NewWkBkName).Activate
gt; For iCtr = 1 To Worksheets.Count
gt; Sheets(iCtr).Select
gt; Cells.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Next iCtr
gt;
gt; --
gt; DCSwearingen
gt;
gt; ------------------------------------------------------------------------
gt; DCSwearingen's Profile: www.excelforum.com/member.php...oamp;userid=21506
gt; View this thread: www.excelforum.com/showthread...hreadid=542592

--

Dave Peterson

yes I do!

quot;Adrianquot; wrote:

gt; Yes that is exactly what I need.
gt;
gt; quot;Dave Oquot; wrote:
gt;
gt; gt; Do you need a dated tab name for every day of each month?
gt; gt;
gt; gt;