Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
I have created a worksheet that we need to copy and label January through
December, is tere a way to do this automatically? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
Sub AddSheets()
Dim sh As Worksheet Dim i As Long With ActiveWorkbook For i = 1 To 12 Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Cou nt)) 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) "DonV" wrote in message ... I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
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 DonV wrote: I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
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.Co unt)) ActiveSheet.Name = MonthName(Month:=ActiveSheet.Index - wkcount, abbreviate:=True) End With Next End Sub -- Gary "Dave Peterson" wrote in message ... 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 DonV wrote: I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
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" wrote in message ... Sub AddSheets() Dim sh As Worksheet Dim i As Long With ActiveWorkbook For i = 1 To 12 Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Cou nt)) 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) "DonV" wrote in message ... I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
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" wrote in message ... 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" wrote in message ... Sub AddSheets() Dim sh As Worksheet Dim i As Long With ActiveWorkbook For i = 1 To 12 Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Cou nt)) 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) "DonV" wrote in message ... I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
Thanks
"Bob Phillips" wrote in message ... 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" wrote in message ... 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" wrote in message ... Sub AddSheets() Dim sh As Worksheet Dim i As Long With ActiveWorkbook For i = 1 To 12 Set sh = .Worksheets.Add(after:=.Worksheets(.Worksheets.Cou nt)) 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) "DonV" wrote in message ... I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
One for every month
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? "DonV" wrote in message ... I have created a worksheet that we need to copy and label January through December, is tere a way to do this automatically? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |