Refresh Pivot - caught in a loop

**jomili** · 11-16-2011, 01:54 PM

I'm having trouble with the "Refresh" part of the macro below. When the macro triggers, if I watch it, the screen just keeps flickering. When I hit escape, I get a Run-Time 1004 error saying "RefreshTable method of PivotTable Class failed". It's a simple bit of code, so I don't know why it would be messing up. Any help troubleshooting this is greatly appreciated.

Please Login or Register  to view this content.

**jomili** · 11-16-2011, 02:54 PM

Okay, I discovered the cause of the problem, but not the solution. The last part of my macro pastes the updated data back into the "MFR Adjustments" sheet, which then triggers the WorksheetActivate event, which retriggers the macro, and round and round it goes.

I don't know how to proceed from here, so if anyone has an idea now would be the time to suggest it.

Thanks,
John

**Steffen Thomsen** · 11-16-2011, 02:57 PM

Then insert at the top of your macro

Please Login or Register  to view this content.

And at the buttom

Please Login or Register  to view this content.

**jomili** · 11-16-2011, 03:11 PM

Hmm, it helps, but it's not there.

A little background: my users will make changes to their Projections page. ProjPivot is a sheet containing a pivot based on that Projections page. When they make a change, at some point they'll want to go to the MFR Adjustments page to see how that affects everything on that page. So, when they activate MFR Adjustments, I want my macro to run, to update all the values.

What's happening now (after the Enable Events changes):

If ProjPivot is my active page, and I run the macro, it runs perfectly.

If Projections is my active page, and I enter a change, and then select MFR Adjustments, the macro does not work correctly.

It doesn't generate an error, but columns H and I in the ProjPivot (and so columns A and B in MFR Adjustments) only contain a header, with nothing underneath.

Any other ideas?

**jomili** · 11-16-2011, 03:23 PM

Okay, I found a way, but I don't like it.

After "Enable Events false" if I Activate my ProjPivot sheet, then run the code, then activate MFR Adjustments, then re-enable events, it works.

I really don't want to activate and reactivate my sheets like that, but I can live with it if I have to.

**JieJenn** · 11-16-2011, 03:28 PM

Just a recommendation. Instead of using Range, try cell(x,y) to extract the last row number

For example,
LR = Cells(rows.count, "X").end(xlup).row

**Steffen Thomsen** · 11-16-2011, 03:33 PM

The problem lies with the Worksheet_change-event.

Maby if you macro gave some indication to the Worksheet_change event it would be better.

At the top off your macro place

Please Login or Register  to view this content.

At the buttom

Please Login or Register  to view this content.

In your worksheet_change event place an outer if
If the range specified above = 1 then dont do anything
Else Do whats there.

Steffen Thomsen

**jomili** · 11-16-2011, 03:51 PM

Jie Jenn,
So I'd replace X with the column number I want to use for my last row, right? I tested it, and it seems to work just fine, but why would it be superior in any way?

Steffen,
I tried your suggestion. If I take away my two "Activate" statements, then whether I use your suggestion or not the macro still has the same problem.

**Steffen Thomsen** · 11-16-2011, 03:53 PM

Try refering the full path to the data whenever you have a range.

Sheets(name).Range(range) and so on, it's somewhere in these references the code fails!

**jomili** · 11-18-2011, 09:42 AM

Well, I had a well thought out answer, articulated beautifully, and it disappeared into cyberspace somewhere, so here's my cheap and easy final response: I went another route, using VLookups instead on the MFR Adjustments page, which means I don't have to do any page changing, which takes away the problem of getting stuck in a loop. So, life is good, God is in charge, and all is well.

Thanks for all the help, and JieJenn, I'm still curious as to why your lastrow formula would be superior.

Thanks,
John