Change Worksheet Name to today's date

Question

I want to change the sheet name to today's date every day.

Sub GetPivotData()

    Dim sToday As String
    sToday = Format(Date, "dd.mm.yy")

    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(GETPIVOTDATA(""Opportunity"",'28.06.16'!R34C28,""Status"",""Open"",""Probability"",20,""Sales Territory"",R[2]C[6]),0)"

    Range("C3").Select

End Sub

I want to change the '28.06.16' to 'sToday'.

Answer 1

try this:

Application.workbooks("YourWorkbook").sheets("YourSheetname").name = sToday

Answer 2

Concatenate the variable into the formula string.

Option Explicit

Sub GetPivotData()

    Dim sToday As String
    sToday = Format(Date, "dd.mm.yy")

    With Worksheets("Sheet1")    '<~~ you should know what worksheet you are on!
        .Range("C2").FormulaR1C1 = _
            "=IFERROR(GETPIVOTDATA(""Opportunity"",'" & sToday & "'!R34C28,""Status"",""Open"",""Probability"",20,""Sales Territory"",R[2]C[6]),0)"

    End With

End Sub