can you autofill 12 worksheets each with the name of relevant mon.

B

beebop

most of my work is done on a monthly basis, so i would like to know if there
is a quick way of setting up new workbooks with 12 worksheets each containing
the name of the relevant month on the sheet tab and in cell a1 on the
worksheet
 
D

Don Guillett

You should just make a Template and use that but this will do it. The myarry
line is ONE line

Sub NewMonthlyWB()
myarray = Array("Dec", "Nov", "Oct", "Sep", "Aug", "Jul", "Jun", "May",
"Apr", "Mar", "Feb", "Jan")
Workbooks.Add
With ActiveWorkbook
For Each i In myarray
Sheets.Add.Name = i
Sheets(i).Range("a1") = i
Next
Application.DisplayAlerts = False
For Each sh In Sheets
If Len(sh.Name) > 3 Then sh.Delete
Next
Application.DisplayAlerts = True
End With
End Sub
 
G

Gord Dibben

beebop

Create a new workbook with one sheet named Sheet1.

Enter January through December in A1:A12 on Sheet1.

Run this macro then save the workbook as a Template named BOOK(do not add the
..XLT, Excel will do that for you).

Sub Add_New_Worksheets()
'On Sheet1 enter January to December down column A.
Dim mySheet1 As Worksheet
Dim mySheet2 As Worksheet
Dim myCell As Range

Set mySheet1 = Worksheets("Sheet1")
For Each myCell In Range("A1:A12")
Set mySheet2 = Sheets.Add(Type:="Worksheet")
mySheet1.Activate
mySheet1.Range("A1").Value = myCell.Value
mySheet2.Name = mySheet1.Range("A1").Value
mySheet2.Range("A1").Value = mySheet1.Range("A1").Value
Next myCell
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub


Gord Dibben Excel MVP
 
Top