Help with crating a tab based on cell value

[menor59]

I have the following in my "ThisWorkBook"


Private Sub Workbook_Open()
Worksheets("Blank").Activate
Run "HideRibbon"
End Sub


what im trying to do is create a work sheet called 'Report for AA17' (AA17 is in dd-MMM-yy format, but the tab should read 'Report for MMM dd, YYYY') when the workbook is opened, and if the sheet is already created and present dont create...

thoughts?

Thank you...

[SamT]

Here is a generic sheet adder I use. It only works in the workbook the code module is in due to the use of "ThisWorkbook." The function always returns Sheets(NewSheetName), (even if it already exists,) and Activates it.


Public Function MakeNewSht(NewSheetName As String, AfterSheet As String) As Worksheet
Dim NewSht As Worksheet

'Find or make worksheet
On Error GoTo SheetDoesntExist
Set NewSht = Sheets(NewSheetName)
GoTo SheetExists
SheetDoesntExist:
Set NewSht = ThisWorkbook.Worksheets.Add(After:=Sheets(AfterSheet))
NewSht.Name = NewSheetName
SheetExists:
NewSht.Activate
Set MakeNewSht = NewSht
End Function
for the New Sheet Name, try:

MyDate = Range("AA17").Value
NewName = Format(MyDate, "MMM, dd, yyyy")
newName = "Report For " & NewName