I am trying to create a workbook with 26 sheets (26 - 2 wk scheduling) for
work.
I was wondering if a macro could execute via command button to copy a
template for a two week schedule 26 times AND label the sheet tab with the
date of the payperiod, ie Mar 2 - Mar 15?

If so, what would the code need to be for copy and labeling?

Thanks,
Les

Try something like the following. Change the name of the template sheet in
the line marked with <<<<.

Sub AAA()
Dim DT As Date
Dim ws As Worksheet
Dim N As Long
Const TEMPLATE_SHEET_NAME = "TSheet" '<<< CHANGE

On Error Resume Next
DT = CDate(InputBox("Enter start date:"))
On Error GoTo 0
If DT = 0 Then
Exit Sub
End If

Application.ScreenUpdating = False
For N = 1 To 26
With ThisWorkbook.Worksheets
.Item(TEMPLATE_SHEET_NAME).Copy after:=.Item(.Count)
Set ws = ActiveSheet
ws.Name = Format(DT, "mmm dd") & " - " & _
Format(DT + 13, "mmm dd")
DT = DT + 14
End With
Next N
Application.ScreenUpdating = False
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"WLMPilot" wrote in message
...
I am trying to create a workbook with 26 sheets (26 - 2 wk scheduling) for
work.
I was wondering if a macro could execute via command button to copy a
template for a two week schedule 26 times AND label the sheet tab with the
date of the payperiod, ie Mar 2 - Mar 15?

If so, what would the code need to be for copy and labeling?

Thanks,
Les

Thanks Chip. Looks like exactly what I needed.

Les

"Chip Pearson" wrote:

Try something like the following. Change the name of the template sheet in
the line marked with <<<<.

Sub AAA()
Dim DT As Date
Dim ws As Worksheet
Dim N As Long
Const TEMPLATE_SHEET_NAME = "TSheet" '<<< CHANGE

On Error Resume Next
DT = CDate(InputBox("Enter start date:"))
On Error GoTo 0
If DT = 0 Then
Exit Sub
End If

Application.ScreenUpdating = False
For N = 1 To 26
With ThisWorkbook.Worksheets
.Item(TEMPLATE_SHEET_NAME).Copy after:=.Item(.Count)
Set ws = ActiveSheet
ws.Name = Format(DT, "mmm dd") & " - " & _
Format(DT + 13, "mmm dd")
DT = DT + 14
End With
Next N
Application.ScreenUpdating = False
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"WLMPilot" wrote in message
...
I am trying to create a workbook with 26 sheets (26 - 2 wk scheduling) for
work.
I was wondering if a macro could execute via command button to copy a
template for a two week schedule 26 times AND label the sheet tab with the
date of the payperiod, ie Mar 2 - Mar 15?

If so, what would the code need to be for copy and labeling?

Thanks,
Les

Chris,

I may be reaching on this one, but thought I would ask. As stated below
(and you answered my question), I am trying to create a workbook that will be
as automated as possible in creating an annual schedule. I am in the early
stages of coming up with different ways to do this.

I want the scheduler to be able to simply fill in the blanks (shifts) with
names after the 26 different payperiods are created. Along this thinking, I
was wondering about the following and if it is possible:

1) Once commandbutton is clicked to execute code for creating the 26 sheets
and naming them, the commandbutton is either hidden or disabled for good. I
believe this can be easily done.

2) Create 26 commandbuttons, naming each with matching sheet names and
aligning them on a sheet. I am pretty sure this can be done.

3) Automatically place the code for each of the 26 commandbuttons so that
they are "working" at the point of creating them. This is the one I am
probably reaching on, but I was wondering, since the sheet name and
commandbutton name (captions) are the same then maybe it might work. Or
maybe the sheet can be referenced via sheet index number.

I thank you for your past help and am interested in these three items,
especially #3.

Thanks,
Les

"Chip Pearson" wrote:

Try something like the following. Change the name of the template sheet in
the line marked with <<<<.

Sub AAA()
Dim DT As Date
Dim ws As Worksheet
Dim N As Long
Const TEMPLATE_SHEET_NAME = "TSheet" '<<< CHANGE

On Error Resume Next
DT = CDate(InputBox("Enter start date:"))
On Error GoTo 0
If DT = 0 Then
Exit Sub
End If

Application.ScreenUpdating = False
For N = 1 To 26
With ThisWorkbook.Worksheets
.Item(TEMPLATE_SHEET_NAME).Copy after:=.Item(.Count)
Set ws = ActiveSheet
ws.Name = Format(DT, "mmm dd") & " - " & _
Format(DT + 13, "mmm dd")
DT = DT + 14
End With
Next N
Application.ScreenUpdating = False
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"WLMPilot" wrote in message
...
I am trying to create a workbook with 26 sheets (26 - 2 wk scheduling) for
work.
I was wondering if a macro could execute via command button to copy a
template for a two week schedule 26 times AND label the sheet tab with the
date of the payperiod, ie Mar 2 - Mar 15?

If so, what would the code need to be for copy and labeling?

Thanks,
Les