Auto Refresh pivots and totals

**Ivor** · 06-09-2011, 05:51 AM

Good morning all

If it's not one thing, then it's another with this workbook of mine. Firstly I'd like to say thank you to all who have kindly helped me so far; hopefully this is my last issue to solve.

I have a workbook which has a pivot table sheet (KRI Details) and a summary sheet (All SLA Data). My current macro is one continuous sub string which includes the following code (provided by DaveGugg)

Please Login or Register  to view this content.

In summary, the above code looks up a range on 'KRI Details', finds the totals, and places it onto a corresponding cell in 'All SLA Data' (re the Hlookup reference). This works great when running the macro; However after the macro has been run, if I decide to make a change to the source data, once I hit refresh, the pivot totals change but the summary sheet totals (which are supposed to be fed from the pivots) remain the same. Apart from adding additional code to create a button that will perhaps repeat the whole totalling process, is there a quicker way to refresh everything so that the summary sheet 'All SLA Data' will update along with the pivot sheet 'KRI Details'?

Apologies if it sounds a little confusing, and I have attached a copy of the sheets in question.

Many thanks
Ivor

**Ivor** · 06-09-2011, 06:59 AM

Actually, a way around this would be for me to replace that current VBA code and simply use VBA to place the 'HLOOKUP' formula into certain cells. This means the cells will always update from the 'HLOOKUP' range. Therefore, could somebody help tweek the code below so that it will work? I keep getting an error saying "Expected: End of Statement"

#Sub GetPivotTotals()
Sheets("All SLA Data").Select
Range("D3").FormulaLocal ="=HLOOKUP("Pass",'KRI Details'!A4:J9,6,0)"
Range("D4").FormulaLocal ="=HLOOKUP("Fail",'KRI Details'!A4:J9,6,0)"
Range("D5").FormulaLocal ="=HLOOKUP("Miss SLA",'KRI Details'!A4:J9,6,0)"
Range("D6").FormulaLocal ="=HLOOKUP("Expected",'KRI Details'!A4:J9,6,0)"
Range("D7").FormulaLocal ="=HLOOKUP("Pot Fail",'KRI Details'!A4:J9,6,0)"

End Sub#

**Ivor** · 06-09-2011, 07:34 AM

HA HA HA HA Well I never thought I'd come on this forum, post an issue, reply and sort it myself, but I did ha ha haha.

All sorted now and everything is working fine. The issue with my last code was I was missing an additional pair of inverted commas.

Thanks to....well...me I guess

Cheers all

Ivor

**teylyn** · 06-09-2011, 07:35 AM

Ivor,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here