Create and label worksheets

[John Pierce]

I have a form on a single worksheet. I would like to run a macro
that replicates the sheet for each day of a month and uses each
day's date as a sheet tab label (format: Monday 05/21/07) and puts
that date on the sheet.

 

[Jon Peltier]

This procedure will do it. Note that the sheet name format uses "-" instead
of "/", because slashes are not allowed in a sheet name. Call it like this:

AddSheets Now

or if cell A1 in the active sheet contains a date

AddSheets ActiveSheet.Range("A1").Value

Sub AddSheets(MyDate As Date)
Dim iDate As Date

For iDate = DateSerial(Year(MyDate), Month(MyDate), 1) To
DateSerial(Year(MyDate), Month(MyDate) + 1, 0)
ActiveWorkbook.Worksheets.Add
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
With ActiveSheet
With .Range("A1")
.Value = iDate
.NumberFormat = "dddd mm/dd/yy"
End With
.Name = Format(iDate, "dddd mm-dd-yy")
End With
Next
End Sub

The procedure will crash if it encounters a duplicate sheet name.

- Jon

 

[Don Guillett]

Sub mynewsheets()
For i = 30 To 1 Step -1
On Error Resume Next
Sheets.Add.Name = i
Next i
End Sub

 

[Guest]

Just some added information:
to replicate a template sheet as I believe you said you wished to do, change


ActiveWorkbook.Worksheets.Add
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

to

With ActiveWorkbook
.Worksheets("Template").copy After:= _
ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
End With

Change template to reflect the name of your "form" sheet.

 

[Jon Peltier]

Oh yeah, I missed that. Thanks, Tom.

- Jon

 

[John Pierce]

Oh yeah, I missed that. Thanks, Tom.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______








- Show quoted text -

Thanks for the code. I have created the procedure but for some reason
I cannot see it from the worksheet. It won't even run from the code
module when I press F5. The Macro dialog box appears and when I enter
the name of the macro it starts a module to create it. What gives?

 

[Jon Peltier]

The macro starts as

Sub AddSheets(MyDate As Date)

which means you must enter a date. This gives you the flexibility of
creating next months sheets ahead of time, or backtracking and creating a
previous month's sheets. As I said in my initial post

Call it like this:

AddSheets Now

or if cell A1 in the active sheet contains a date

AddSheets ActiveSheet.Range("A1").Value

Either of these lines that call the sub can be inserted into an existing VBA
procedure or run from the Immediate Window.

- Jon

 

[John Pierce]

The macro starts as

Sub AddSheets(MyDate As Date)

which means you must enter a date. This gives you the flexibility of
creating next months sheets ahead of time, or backtracking and creating a
previous month's sheets. As I said in my initial post

Call it like this:

AddSheets Now

or if cell A1 in the active sheet contains a date

AddSheets ActiveSheet.Range("A1").Value

Either of these lines that call the sub can be inserted into an existing VBA
procedure or run from the Immediate Window.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______







- Show quoted text -

Thanks very much to Jon and Tom. Problem solved. I learned something
very important here. I had been told before that a procedure that
takes an argument won't show up in the Macro list but could be run by
typing its name in the Macro dialog box or by assigning it to a
command button (how if it doesn't show?) - neither of which worked for
me. It seems that such a procedure has to be called from another
procedure (which can be assigned to a button). I couldn't run it from
the Immediate window either, though. Thanks again for the help.

 

[Jon Peltier]

What error came up when you tried it from the Immediate Window?

- Jon