copy worksheet from previous month and rename to current month

[Guest]

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.

 

[Brian]

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"

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.

 

[Rowan Drummond]

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

 

[Guest]

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]

You're welcome.

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

: