renaming new worksheet with time stamp and text

**beckdog** · 05-22-2012, 06:38 PM

I am new to VBA and currently working on a dashboard-ish project. My code needs to create a new tab to store downloaded data from a SQL database AND rename the tab as "Financial_Data_MM-DD-YY" where MM-DD-YY represent the current date.

What I have already complete is displayed below. Can anyone help a brotha out and explain how to add text into this naming convention?

Sub CreateRenameDataSheet()

   Dim i As Integer, Cntr As Integer
        
    Sheets.Add
    For i = 1 To Sheets.Count
        If Left(Sheets(i).Name, 10) = Format(Date, "DD-MM-YYYY") Then
            Cntr = Cntr + 1
        End If
    Next
    If Cntr = 0 Then
        ActiveSheet.Name = Format(Date, "DD-MM-YYYY")
    Else
        ActiveSheet.Name = Format(Date, "DD-MM-YYYY") & " (" & Cntr & ")"
    End If
        
End Sub

**shg** · 05-22-2012, 06:45 PM

One way:

Sub CreateRenameDataSheet()
    Const sWks      As String = "Financial_Data_"
    Dim sDate       As String
    Dim wks         As Worksheet
    Dim n           As Integer

    sDate = Format(Date, "DD-MM-YYYY")

    For Each wks In Worksheets
        If InStr(wks.Name, sDate) Then n = n + 1
    Next

    If n = 0 Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sWks & sDate
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sWks & sDate & " (" & n & ")"
    End If
End Sub