How to make data source for pivot table always be the activesheet?

[Bee Cee]

I have a file that is updated daily (each new day is a different tab) on which I've created a pivot table to summarize the data on that particular sheet. I would like to always designate the data location to be the active sheet, since nothing changes. Can anyone help me understand how to do this? Thank you!

 

[Worf]

Welcome

' tested on Windows!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Data_sht As Worksheet, ps As Worksheet, pn$
If MsgBox("Update pivot table?", vbYesNo) = vbNo Then Exit Sub
Set Data_sht = Sh
Set ps = ThisWorkbook.Worksheets("pivot")
pn = "PivotTable2"
ps.PivotTables(pn).ChangePivotCache ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Data_sht.Name & "!" & _
Data_sht.[a1].CurrentRegion.Address(ReferenceStyle:=xlR1C1))
ps.PivotTables(pn).RefreshTable
MsgBox pn & "'s data source range has been updated!"
End Sub

 

[Bee Cee]

Welcome

' tested on Windows!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Data_sht As Worksheet, ps As Worksheet, pn$
If MsgBox("Update pivot table?", vbYesNo) = vbNo Then Exit Sub
Set Data_sht = Sh
Set ps = ThisWorkbook.Worksheets("pivot")
pn = "PivotTable2"
ps.PivotTables(pn).ChangePivotCache ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Data_sht.Name & "!" & _
Data_sht.[a1].CurrentRegion.Address(ReferenceStyle:=xlR1C1))
ps.PivotTables(pn).RefreshTable
MsgBox pn & "'s data source range has been updated!"
End Sub

Hi, Thank you for posting a reply! I tried this but I'm missing something somewhere along the way. I'd like to attach a copy of my file; the first sheet is the one that gets duplicated (with a macros), and for subsequent sheets I manually change the data source to reflect the data on that specific page. This action is what I was hoping to automate. Is there any way to upload the file? - Bee

 

[Worf]

Hi

You can upload it to a hosting site like Drop Box and paste the link here.

 

[Bee Cee]

Sorry it took a while for me to reply, thank you for your patience!

I've uploaded it to DropBox as you recommended, here is the link: Mock CC Dep.xlsm

 

[Worf]

I am having a busy week, will look into this during the weekend.

 

[Bee Cee]

No worries.
Grateful.

 

[Worf]

Hi

I tested this code on your workbook, note where to paste the code. Tell me if you need my test workbook.

' ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Data_sht As Worksheet, ps As Worksheet, pn$
If MsgBox("Update pivot table?", vbYesNo) = vbNo Then Exit Sub
Set Data_sht = Sh
Set ps = ThisWorkbook.Worksheets("first")
pn = "PivotTable1"
ps.PivotTables(pn).ChangePivotCache ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Data_sht.Name & "!" & _
Data_sht.[a1].CurrentRegion.Address(ReferenceStyle:=xlR1C1))
ps.PivotTables(pn).RefreshTable
MsgBox pn & "'s data source range has been updated!"
End Sub

 

[Bee Cee]

Hi, I have no idea where to paste the code! I took a screenshot, thank you so much for your guidance.

 

[Bee Cee]