Macro to make a new tab based on month

[TheBlackDragon7]

I've been trying to build a macro that will create a new tab whenever it's a new month. I have a general idea of how to do it, but I can't get the syntax right for naming the tab the name of the new month. The worksheet that would be copied and placed at the end is named "Template"

 

[TheBlackDragon7]

This is the code I have so far:

    Sheets("Template").Select
    Sheets("Template").Copy After:=Sheets(2)
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = "June"
    Range("A4").Select
End Sub

But where it says "June" it needs to name the tab the current month, and the macro needs to run when the month is different from when the book was opened last. Any help is appreciated!

 

[jindon]

try

Sheets("Template").Copy After:=Sheets(2)
ActiveSheet.Name = MonthName(Month(Date),False)

 

[Fazza]

Hi. Maybe like this in the workbook's code module? It checks first if the sheet exists, if not, it adds the new one. HTH, Fazza

Option Explicit
 
Private Sub Workbook_Open()
 
  If Not SheetExists(Format$(Date, "mmmm yyyy")) Then
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Format$(Date, "mmmm yyyy")
  End If
End Sub
 
Private Function SheetExists(sname) As Boolean
  'from J Walkenbach
  Dim x As Object
  On Error Resume Next
  Set x = ActiveWorkbook.Sheets(sname)
  If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

 

[TheBlackDragon7]

Thanks jindon!

Here's the code I wrote with that information:

Private Sub Workbook_Open()
'
' New_month Macro
' Update with new month tab
'
If ActiveSheet.Name = MonthName(Month(Date), False) Then

Else

Sheets("Template").Copy After:=Sheets(2)
ActiveSheet.Name = MonthName(Month(Date), False)
    Range("A4").Select
    
End If
End Sub

It works fine, if you or anyone else has any suggestions on cleaning it up a bit or making it more reliable, I'm all ears!

Thanks again!

 

[jindon]

try

Private Sub Workbook_Open()
Dim ws As Worksheet, wsName As String
wsName = MonthName(Month(Date, False)
On Error Resume Next
Set ws = Me.Sheets(wsName)
On Error GoTo 0
If ws Is Nothing Then
    Me.Sheets("Template").Copy After:=Me.Sheets(2)
    ActiveSheet.Name = wsName
End If
End Sub