Macro for Excel tabs

[amattey]

Hello,

Can anyone help me with a code that will copy a complete sheet including the macros within it and create another tab within the same workbook and name the sheet with the current month and year? Kinda like when I run that macro it will create a tab with the tab name for example "August, 2011" and will be an exact copy of the original sheet including its macros.

 

[Ruddles]

Something like this?

Sub NewMonthMacro()
 
  ActiveSheet.Copy After:=Sheets(Sheets.Count)
  ActiveSheet.Name = Format(Now(), "mmmm, yyyy")
 
End Sub

There's no error checking in that code so it will fail if the sheet already exists

 

[Ruddles]

With error checking:-

Sub NewMonthMacro()
 
  ActiveSheet.Copy After:=Sheets(Sheets.Count)
  On Error Resume Next
  ActiveSheet.Name = Format(Now(), "mmmm, yyyy")
  If Err.Number = 0 Then
    MsgBox "Sheet '" & Format(Now(), "mmmm, yyyy") & "' created." _
         & Space(10), vbOKOnly + vbInformation
  End If
  If Err.Number = 1004 Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    MsgBox "Sheet '" & Format(Now(), "mmmm, yyyy") & "' already exists!" _
         & Space(10), vbOKOnly + vbExclamation
  End If
  On Error GoTo 0
 
End Sub

 

[amattey]

Thank you, it works awesome!

 

[Ruddles]

This is an awesome place!

 

[amattey]

Indeed!

 

[amattey]

Is there a macro I can also run to take me from the current tab to the tab that was created with the current month and year as you helped me above?

 

[Ruddles]

I didn't expect you to ask me that... doesn't the new sheet get activated by default?

 

[amattey]

Yes it does but I have a fixed sheet that I want to make sure that the fixed sheet can be linked directly to the new one created.

 

[Ruddles]

Ah! Something like this then:-

Sub JumpToThisMonth()
 
  On Error Resume Next
  Sheets(Format(Now(), "mmmm, yyyy")).Activate
  If Err.Number = 9 Then
    MsgBox "Sheet '" & Format(Now(), "mmmm, yyyy") & "' doesn't exist!" _
         & Space(10), vbOKOnly + vbExclamation
  End If
  On Error GoTo 0
 
End Sub