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.
>
|
Similar Threads
