copy worksheet from previous month and rename to current month
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.
|