MrExcel Publishing
Your One Stop for Excel Tips & Solutions

add sheets and nammed

Posted by amroo on April 05, 2001 7:53 AM

How can I automatically add a sheet in a specific worbook (stock) when I open it and it be nammed as the month whe are on, so I got at the end of the year 12 sheets. (that's under a macro of course)
2001 thanks

Posted by Barrie Davidson on April 05, 2001 8:49 AM

Try this code "On Open" for your workbook, I think it will work for you. I added some questions for the user to ensure you want to add the worksheet.

Sub Insert_Month_Sheet()

Dim Month_Number As Integer
Dim Month_Text(12) As String
Dim Sheet_Name As String
Dim answer As Integer
Dim Sheet_count As Variant
Dim Counter As Integer

Month_Text(1) = "January"
Month_Text(2) = "February"
Month_Text(3) = "March"
Month_Text(4) = "April"
Month_Text(5) = "May"
Month_Text(6) = "June"
Month_Text(7) = "July"
Month_Text(8) = "August"
Month_Text(9) = "September"
Month_Text(10) = "October"
Month_Text(11) = "November"
Month_Text(12) = "December"
Month_Number = Month(Date)
Sheet_Name = Month_Text(Month_Number)
answer = MsgBox("Add worksheet for " & Sheet_Name, vbYesNo)
If answer = 7 Then
Exit Sub
End If
Sheet_count = ActiveWorkbook.Sheets.Count
Counter = 1
Do Until ActiveSheet.Index = Sheet_count
If ActiveSheet.Name = Sheet_Name Then
answer = MsgBox("A sheet already exists for " & Sheet_Name & ". Continue?", _
vbYesNo + vbQuestion)
If answer = 7 Then
Exit Sub
ActiveSheet.Name = Sheet_Name & " (2)"
Exit Sub
End If
End If
Counter = Counter + 1
ActiveSheet.Name = Sheet_Name

End Sub

Posted by amroo on April 05, 2001 11:27 PM

Thank you to Barrie Davidson for the help.
I will inform you about the result in my application.
2001 Best