Create and label worksheets
 

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.

Create and label worksheets
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John Pierce" wrote in message
ups.com...
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.

Create and label worksheets
 

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

"John Pierce" wrote in message
ups.com...
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.

Create and label worksheets
 

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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John Pierce" wrote in message
ups.com...
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.

Create and label worksheets
 

Oh yeah, I missed that. Thanks, Tom.

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


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John Pierce" wrote in message
ups.com...
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.

Create and label worksheets
 

On May 26, 8:22 am, "Jon Peltier"
wrote:
Oh yeah, I missed that. Thanks, Tom.

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

"Tom Ogilvy" wrote in message

...



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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"JohnPierce" wrote in message
roups.com...
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.- Hide quoted text -

- 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?

Create and label worksheets
 

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
_______


"John Pierce" wrote in message
ps.com...
On May 26, 8:22 am, "Jon Peltier"
wrote:
Oh yeah, I missed that. Thanks, Tom.

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

"Tom Ogilvy" wrote in message

...



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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"JohnPierce" wrote in message
roups.com...
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.- Hide quoted text -

- 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?

Create and label worksheets
 

On May 28, 9:40 am, "Jon Peltier"
wrote:
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
_______

"JohnPierce" wrote in message

ps.com...



On May 26, 8:22 am, "Jon Peltier"
wrote:
Oh yeah, I missed that. Thanks, Tom.

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

"Tom Ogilvy" wrote in message

...

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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"JohnPierce" wrote in message
roups.com...
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.- Hide quoted text -

- 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?- Hide quoted text -

- 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.

Create and label worksheets
 

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

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


"John Pierce" wrote in message
ps.com...
On May 28, 9:40 am, "Jon Peltier"
wrote:
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
_______

"JohnPierce" wrote in message

ps.com...



On May 26, 8:22 am, "Jon Peltier"
wrote:
Oh yeah, I missed that. Thanks, Tom.

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

"Tom Ogilvy" wrote in message

...

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.

--
Regards,
Tom Ogilvy

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"JohnPierce" wrote in message
roups.com...
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.- Hide quoted text -

- 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?-
Hide quoted text -

- 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.