Creating new worksheets that are named to be dates

[miamidawgs]

Hi, I am new to VBA and trying to create a form for a gas station. The form is made so a user can enter sales information and it will calculate month to date totals as pages are added through a new day button at the top. The button should create a new sheet that is named as the next day. The code I have works when u press it once but if it is pressed a second time it doesn't change the date. Is there a better way I can do this?

Private Sub CommandButton1_Click()
    Dim strTemplate As String: strTemplate = "C:\Users\Saif\Documents\Custom Office Templates\GasStationForm.xltm"
    Dim counter As Integer
    counter = counter + 1
    Sheets.Add After:=Sheets(Sheets.Count), Type:=strTemplate
    ActiveSheet.Name = Format(Date + counter, "YYYY-MM-DD")
End Sub

 

[sadboy309]

You try again:

Public counter As Integer


Private Sub CommandButton1_Click()
    Dim strTemplate As String
    strTemplate = "C:\Users\Saif\Documents\Custom Office Templates\GasStationForm.xltm"
    
    counter = counter + 1
    Sheets.Add After:=Sheets(Sheets.Count), Type:=strTemplate
    ActiveSheet.Name = Format(Date + counter, "YYYY-MM-DD")
End Sub

 

[Fluff]

How about

Private Sub CommandButton1_Click()
    Dim strTemplate As String: strTemplate = "C:\Users\Saif\Documents\Custom Office Templates\GasStationForm.xltm"
   [COLOR=#ff0000] Static counter As Long[/COLOR]
    counter = counter + 1
    Sheets.Add After:=Sheets(Sheets.Count), Type:=strTemplate
    ActiveSheet.Name = Format(Date + counter, "YYYY-MM-DD")
End Sub

 

[miamidawgs]

That doesn't work, I get the same issue with that code

 

[Fluff]

Which code are you referring to?

 

[miamidawgs]

I tried both of them and they give the same result

 

[Fluff]

Are you closing the workbook in between running the code?

 

[miamidawgs]

No but thanks I figured it out

 

[Fluff]

Glad you sorted it & thanks for the feedback