Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

**Rhino_dance** · 07-22-2015, 11:11 AM

I have prices on an Excel sheet and these prices are used in a calculated column in POWERPIVOT. I then have a pivot table in Excel that sources its data from this POWERPIVOT table. So it's a triangulated setup.

I was hoping I could change the price in Excel and see that price change reflected in the Excel pivot table. But the data does not change even if I refresh the pivot table. The only way I can get the new price to flow through to the pivot table is if I actually open up POWERPIVOT (or already have it open) and also make it the active app by clicking on it. Then it refreshes. Is there some way to make this refresh happen without opening and activating POWERPIVOT? How about making the refresh happen automatically whenever the price is changed?

Thanks!

**AlexandraT** · 07-23-2015, 05:41 AM

Have you tried the Refresh All option? I honestly only use that since refreshing seems to work sporadically. You can find it if you click on the down arrow below the Refresh button. Keyboard shortcut is Ctrl+Alt+F5.
As far as I know, the pivot wouldn't refresh automatically. The most you could do is to go into Options ->Data and select "Refresh data when opening the file". But that wouldn't help you.
I would also make sure that the pivot's source contains any new additions to the source table. I usually use Insert->Table to make sure any additions are included in the source as opposed to Name ranges in the Names Manager.

Alex

**Rhino_dance** · 07-23-2015, 10:26 AM

Thanks Alex. Yes, 'Refresh All' works! Not sure why I didn't think of that.

I've looked into this problem more and found that I can use the vba command ActiveWorkbook.Model.Refresh (since I'm in Excel 2013) and this will refresh that pivot table that reads from POWERPIVOT. My idea now is to trigger that vba code any time the user changes a prices in column B- but for some reason I can't get the trigger to work.

The boilerplate vba code for monitoring a change in cells B1:B100 is this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
Refresh_Power_Pivot
End If
End Sub

And that triggers the refresh code which is this:

Sub Refresh_Power_Pivot()
ActiveWorkbook.Model.Refresh
End Sub

However when I change a price in one of the B cells the pivot is not refreshing.

Any ideas on why it wouldn't refresh? Again, I appreciate your thoughtful response. -John

**AlexandraT** · 07-23-2015, 11:16 AM

I'm more of a novice in VBA so I can't help you there. Even so, I wouldn't want my pivot to refresh with any change because it might slow everything down. Usually, a power pivot takes a bit longer to refresh and if you have a lot of data, a refresh with every change might just make you want to bash your head against a wall (I work with big data sets and this would not be an option for me, except for the head bashing which I am not particularly fond of). If you're working with small amounts of data it might be useful. However, I also believe you can't undo after a macro runs but a manual refresh can be undone (just in case you want to undo, maybe to compare numbers before and after the change, or if the change was by mistake). That's my 2 cents.

Alex

**Rhino_dance** · 07-23-2015, 03:35 PM

Thanks Alex. The update only takes a few secs but that is with just 1,200 test records. However, as I increase my data size that might not be the case.

Also, I solved the problem I was having. The code to detect the change on the sheet must be in a module dedicated to the specific sheet that contains the range B1:B100. So I had to right click on the specific sheet tab and select 'View Code' and then paste my code into that Module. Cheers, John

Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

LinkBack

Thread Tools

Rate This Thread

Display

Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

Re: Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

Re: Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

Re: Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

Re: Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?

Thread Information

Users Browsing this Thread

Similar Threads

Powerpivot...Show counts where codes tied to an account are in one group but not the other

PowerPivot - Sort by column working in Powerpivot, but not actual pivot table

Powerpivot Calculated Measure to remove blanks from pivot table/chart

PowerPivot Question: How to use powerpivot to analyse multi-column datasets

Inserting a measure in a PowerPivot pivot table to count unique values

Pivot table missing rows using PowerPivot

Getting PowerPivot to Refresh

Bookmarks

Bookmarks

Posting Permissions