copy worksheet from previous month and rename to current month

I have a spreadsheet to track time spent on various projects. I would like
to create a macro (tied to a button) that users can click to copy the
previous months worksheet and rename it to the current month and year.

I.E. I may have a worksheet named "DECEMBER 2005" with current projects
filled in. I would like to copy that sheet and have it renamed to "JANUARY
2006" if I run the macro in Jan of 2006. Similarly, in FEB of 2006 I would
like to copy the JANUARY 2006 sheet and rename to FEBRUARY 2006, and so on.

Here's a set of simple steps... You can just extract the Month name from
the Now() function.

ActiveSheet.Cells.Select
Selection.Copy
Worksheets.Add
Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = "February"




"Dan E." <Dan [email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet to track time spent on various projects. I would
like
> to create a macro (tied to a button) that users can click to copy the
> previous months worksheet and rename it to the current month and year.
>
> I.E. I may have a worksheet named "DECEMBER 2005" with current projects
> filled in. I would like to copy that sheet and have it renamed to
"JANUARY
> 2006" if I run the macro in Jan of 2006. Similarly, in FEB of 2006 I
would
> like to copy the JANUARY 2006 sheet and rename to FEBRUARY 2006, and so
on.

This is taken from another project but does something similar. Assumes
the sheet you want to copy is the last (right most) sheet in the workbook.

Sub NewMonthSheet()
Dim lSht As Worksheet
Dim nSht As Worksheet
Dim shName As String

Set lSht = Sheets(Sheets.Count)

If IsDate(lSht.Name) Then

shName = Format(DateAdd("m", 1, lSht.Name), "mmmm yyyy")

On Error Resume Next 'Tests that sheet doesn't already exist
Set nSht = Sheets(shName)
On Error GoTo 0

If nSht Is Nothing Then
lSht.Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = shName
Else
MsgBox "Sheet """ & shName & """ already exists!" _
, vbCritical
End If
Else
MsgBox "Last sheet name does not" & Chr(10) _
& "represent a month!", vbCritical
End If
End Sub

Hope this helps
Rowan

Dan E. wrote:
> I have a spreadsheet to track time spent on various projects. I would like
> to create a macro (tied to a button) that users can click to copy the
> previous months worksheet and rename it to the current month and year.
>
> I.E. I may have a worksheet named "DECEMBER 2005" with current projects
> filled in. I would like to copy that sheet and have it renamed to "JANUARY
> 2006" if I run the macro in Jan of 2006. Similarly, in FEB of 2006 I would
> like to copy the JANUARY 2006 sheet and rename to FEBRUARY 2006, and so on.

Thanks! That was almost 100% what I wanted to do. I wanted the previous
month to be the 2nd sheet in the workbook, and the new one to become the 2nd
sheet. Here is the modified code:

Sub NewMonthSheet()
Dim lSht As Worksheet
Dim nSht As Worksheet
Dim shName As String

Set lSht = Sheets(2)

If IsDate(lSht.Name) Then

shName = Format(DateAdd("m", 1, lSht.Name), "mmmm yyyy")

On Error Resume Next 'Tests that sheet doesn't already exist
Set nSht = Sheets(shName)
On Error GoTo 0

If nSht Is Nothing Then
lSht.Copy after:=Sheets(1)
Sheets(2).Name = shName
Else
MsgBox "Sheet """ & shName & """ already exists!" _
, vbCritical
End If
Else
MsgBox "Last sheet name does not" & Chr(10) _
& "represent a month!", vbCritical
End If
End Sub

"Rowan Drummond" wrote:

> This is taken from another project but does something similar. Assumes
> the sheet you want to copy is the last (right most) sheet in the workbook.
>
> Sub NewMonthSheet()
> Dim lSht As Worksheet
> Dim nSht As Worksheet
> Dim shName As String
>
> Set lSht = Sheets(Sheets.Count)
>
> If IsDate(lSht.Name) Then
>
> shName = Format(DateAdd("m", 1, lSht.Name), "mmmm yyyy")
>
> On Error Resume Next 'Tests that sheet doesn't already exist
> Set nSht = Sheets(shName)
> On Error GoTo 0
>
> If nSht Is Nothing Then
> lSht.Copy after:=Sheets(Sheets.Count)
> Sheets(Sheets.Count).Name = shName
> Else
> MsgBox "Sheet """ & shName & """ already exists!" _
> , vbCritical
> End If
> Else
> MsgBox "Last sheet name does not" & Chr(10) _
> & "represent a month!", vbCritical
> End If
> End Sub
>
> Hope this helps
> Rowan
>
> Dan E. wrote:
> > I have a spreadsheet to track time spent on various projects. I would like
> > to create a macro (tied to a button) that users can click to copy the
> > previous months worksheet and rename it to the current month and year.
> >
> > I.E. I may have a worksheet named "DECEMBER 2005" with current projects
> > filled in. I would like to copy that sheet and have it renamed to "JANUARY
> > 2006" if I run the macro in Jan of 2006. Similarly, in FEB of 2006 I would
> > like to copy the JANUARY 2006 sheet and rename to FEBRUARY 2006, and so on.
>

You're welcome.

Dan E. wrote:
> Thanks! That was almost 100% what I wanted to do. I wanted the previous
> month to be the 2nd sheet in the workbook, and the new one to become the 2nd
> sheet. Here is the modified code:
>
> Sub NewMonthSheet()
> Dim lSht As Worksheet
> Dim nSht As Worksheet
> Dim shName As String
>
> Set lSht = Sheets(2)
>
> If IsDate(lSht.Name) Then
>
> shName = Format(DateAdd("m", 1, lSht.Name), "mmmm yyyy")
>
> On Error Resume Next 'Tests that sheet doesn't already exist
> Set nSht = Sheets(shName)
> On Error GoTo 0
>
> If nSht Is Nothing Then
> lSht.Copy after:=Sheets(1)
> Sheets(2).Name = shName
> Else
> MsgBox "Sheet """ & shName & """ already exists!" _
> , vbCritical
> End If
> Else
> MsgBox "Last sheet name does not" & Chr(10) _
> & "represent a month!", vbCritical
> End If
> End Sub
>
> "Rowan Drummond" wrote:
>
>
>>This is taken from another project but does something similar. Assumes
>>the sheet you want to copy is the last (right most) sheet in the workbook.
>>
>>Sub NewMonthSheet()
>> Dim lSht As Worksheet
>> Dim nSht As Worksheet
>> Dim shName As String
>>
>> Set lSht = Sheets(Sheets.Count)
>>
>> If IsDate(lSht.Name) Then
>>
>> shName = Format(DateAdd("m", 1, lSht.Name), "mmmm yyyy")
>>
>> On Error Resume Next 'Tests that sheet doesn't already exist
>> Set nSht = Sheets(shName)
>> On Error GoTo 0
>>
>> If nSht Is Nothing Then
>> lSht.Copy after:=Sheets(Sheets.Count)
>> Sheets(Sheets.Count).Name = shName
>> Else
>> MsgBox "Sheet """ & shName & """ already exists!" _
>> , vbCritical
>> End If
>> Else
>> MsgBox "Last sheet name does not" & Chr(10) _
>> & "represent a month!", vbCritical
>> End If
>>End Sub
>>
>>Hope this helps
>>Rowan
>>
>>Dan E. wrote:
>>
>>>I have a spreadsheet to track time spent on various projects. I would like
>>>to create a macro (tied to a button) that users can click to copy the
>>>previous months worksheet and rename it to the current month and year.
>>>
>>>I.E. I may have a worksheet named "DECEMBER 2005" with current projects
>>>filled in. I would like to copy that sheet and have it renamed to "JANUARY
>>>2006" if I run the macro in Jan of 2006. Similarly, in FEB of 2006 I would
>>>like to copy the JANUARY 2006 sheet and rename to FEBRUARY 2006, and so on.
>>