VBA Create a new sheet and then name it using todays date-CodePudding

I am trying to add a new sheet into excel then name it based on today's date. I found the following code on a previous post: Create a new named sheet using a date

Sub NewSheet()
    Dim T As Date, TabName As String
    T = Date
    TabName = "Progress "   Format(T, "mm.d.yyyy")
    ThisWorkbook.Sheets.Add(After:=Sheets("BiWeeklyProgress")).Name = TabName
    
End Sub

This code works perfectly for any new workbook I create and apply it to, however when I apply it to the workbook that I need it for I receive the following error message:

"Compile error:

Wrong number of arguments or invalid property assignment"

I only receive this error when applying the code to my desired workbook. This workbook is connected to a Bloomberg terminal, could that be the issue? Please help!

CodePudding user response：

If you're getting a compile error, then a guess:

TabName = "Progress " & VBA.Format(T, "mm.d.yyyy")

or shorter:

ActiveWorkbook.Sheets.Add(After:=Sheets("BiWeeklyProgress")).Name = "Progress " & _
   VBA.Format(Date, "mm.d.yyyy")

CodePudding user response：

You're making it needlessly complex. Try:

ActiveWorkbook.Sheets.Add(After:=Sheets("BiWeeklyProgress")).Name = "Progress " & _
   Format(Date, "mm.d.yyyy")