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!
 

Worf

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

See the picture below. I know nothing about the Mac implementation of Excel, but you should look for a module called This Workbook.

The code executes automatically every time a sheet is activated.

event.PNG
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
Partial success! When I click on the "Duplicate" button, the new sheet's pivot table updates to the sheet being duplicated, possibly because that script is running before my "duplicate page" script creates a new sheet??
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
If my code runs you must see the corresponding message box.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126

ADVERTISEMENT

I do not think my code is running if the message box does not appear; note that there are actually two of them.

Where exactly did you paste the code?
 

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
I've attached screenshots of where I pasted the code as well as the windows, and the data source once I click "OK" to update.
 

Attachments

  • Screen Shot 2021-02-03 at 1.50.55 PM.png
    Screen Shot 2021-02-03 at 1.50.55 PM.png
    106.5 KB · Views: 4
  • Screen Shot 2021-02-03 at 1.52.26 PM.png
    Screen Shot 2021-02-03 at 1.52.26 PM.png
    88 KB · Views: 4
  • Screen Shot 2021-02-03 at 1.54.07 PM.png
    Screen Shot 2021-02-03 at 1.54.07 PM.png
    225.9 KB · Views: 4
  • Screen Shot 2021-02-03.jpg
    Screen Shot 2021-02-03.jpg
    138.6 KB · Views: 4

Bee Cee

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

ADVERTISEMENT

Thank you for your tenacity!
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
From left to right:

  • This is a message box
  • This is the other message box
  • According to your worksheet layout, is this range correct?
  • The code seems to be in the right place
Is it working in the way you want?
 

Bee Cee

New Member
Joined
Jan 14, 2021
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
The Screen shots uploaded out of order, the first message box asks to update, the second says updated. After the update, the data source is not the active sheet as originally desired, but the page just before the active sheet.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
I wonder if I understood your request correctly… the code is doing what I expect.

Try the version below which will give more information about what is going on.

The code should automatically run every time a sheet is activated.

VBA Code:
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")
MsgBox "Active sheet is " & Sh.Name
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!" & vbLf & _
"Source is " & ps.PivotTables(pn).SourceData
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,020
Members
415,946
Latest member
bellerom

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