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]

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.

 

[Bee Cee]

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]

If my code runs you must see the corresponding message box.

 

[Bee Cee]

Yes, code runs, message box, no.

 

[Worf]

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]

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.

 

[Bee Cee]

Thank you for your tenacity!

 

[Worf]

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]

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]

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.

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