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

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,131
Welcome

VBA Code:
' 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

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
Welcome

VBA Code:
' 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

Well-known Member
Joined
Oct 30, 2011
Messages
4,131
Hi

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

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Oct 30, 2011
Messages
4,131
I am having a busy week, will look into this during the weekend.
 

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

No worries.
Grateful.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,131
Hi

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

VBA Code:
' 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

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
Hi, I have no idea where to paste the code! I took a screenshot, thank you so much for your guidance.
 

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
Screen Shot 2021-02-01 at 10.32.15 AM.png
 

Watch MrExcel Video

Forum statistics

Threads
1,130,143
Messages
5,640,384
Members
417,140
Latest member
whiteprose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top