One for every month

[DonV]

I have created a worksheet that we need to copy and label January through
December, is tere a way to do this automatically?

 

[Bob Phillips]

Sub AddSheets()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

For i = 1 To 12
Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
sh.Name = Format(DateSerial(Year(Date), i, 1), "mmmm")
Next i
End With
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim mCtr As Long
Dim MstrWks As Worksheet
Dim NewWks As Worksheet

Set MstrWks = Worksheets("master")

For mCtr = 12 To 1 Step -1
MstrWks.Copy _
after:=MstrWks
ActiveSheet.Name = Format(DateSerial(2008, mCtr, 1), "MMMM")
'or if you're using xl2002 or newer
'ActiveSheet.Name = MonthName(Month:=mCtr, abbreviate:=False)
Next mCtr
End Sub

 

[Gary Keramidas]

dave:

just a little variant of your code as an example:

Sub name_sheets()
Dim i As Long
Dim wkcount As Long
Dim sh As Worksheet
wkcount = Worksheets.Count
For i = 1 To 12
With ActiveWorkbook
Set sh =
..Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
ActiveSheet.Name = MonthName(Month:=ActiveSheet.Index -
wkcount, abbreviate:=True)
End With
Next
End Sub

 

[DonV]

This code worked thank you. However, is it possible to make it copy an
existing sheet and ues it to make the other sheets?

 

[Bob Phillips]

Sub AddSheets()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

Set sh = ActiveSheet
For i = 1 To 12
sh.Copy After:=.Worksheets(.Worksheets.Count)
ActiveSheet.Name = Format(DateSerial(Year(Date), i, 1), "mmmm")
Next i
End With
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DonV]

Thanks

Sub AddSheets()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

Set sh = ActiveSheet
For i = 1 To 12
sh.Copy After:=.Worksheets(.Worksheets.Count)
ActiveSheet.Name = Format(DateSerial(Year(Date), i, 1), "mmmm")
Next i
End With
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[DonV]

After creating the additional pages in my workbook and saving it the icon
for the workbook had a yellow overlay with an exclamation mark. Does this
mean that something is wrong with the workbook?