Excel VBA to update Sheet Name

[sachin20us]

All,

Hope all is well.
Cell A2 in Data 06/09/2017 has the date 06/09/2017. This date updates according to the current date or on adhoc basis.
Tab Data 06/09/2017 has the data in a table.
Tab Trend 06/09/2017 has a pivot table that updated according to the data 06/09/2017
Tomorrow i have to create two tabs with Data 06/10/2017 and Trend 06/10/2017 as sheet names. The dates are based on date value in cell A2

I have the macro below that dynamically updates my existing pivot table automatically based on the input i give. How to i make the below macro so that instead of pivottables sheet name the macro selects and automatically updates the Trend 06/09/2017 worksheet.

Data tab has the worksheet named data
Pivot table is in the pivottables sheet
In the data tab. i Click on view code and change the macro to Selectionchange....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

 

[JLGWhiz]

Private Sub Worksheet_Activate()
ActiveSheet.Name = "Trend" & Formst(Date, "mm/dd/yyyy")
End Sub

this would change name of the Trend sheet daily as you activate it. I assume you will have somehow preserved the previous days sheet.

 

[sachin20us]

Interesting.. How will i incorporate it in my existing code?
I want the data sheets name to change as well based on the value in a2.
will this update the pivot tables upon selection?

 

[JLGWhiz]

Interesting.. How will i incorporate it in my existing code?
I want the data sheets name to change as well based on the value in a2.
will this update the pivot tables upon selection?

No this code does not update the Pivot Table, it is a separate code utilizing a different event for a trigger to run. This code must reside in the worksheet code module of the sheet which name you want to change. To use it in the Data sheet, you would have a separate code and change the execution line to

ActiveSheet.Name = "Data" & Format(Date, "mm/dd/yyyy")

If you want it to rely on the date in cell A2 of the sheet then

ActiveSheet.Name = "Data" & Format(Range("A2").Value, "mm/dd/yyyy")