Macro or Pivot problem?

[mzspazchick]

I have a problem. When I click on a macro button I created yesterday, my pivot tables refresh with the new data I have entered. Only, my pivot tables appear to be scrambling the data. I have one pivot that calculates a total dollar amount and its $1,400,000 over what it should be for one person.
Could row headings affect this? I have to rows with similar names. For example, one is Invoice Amount and the other is Invoice X Markup.
I have 12 columns of data going into seven pivot tables. All pivot tables are similar in that they have the name of a person and the sum, average or count of something else.
Michelle

 

[mzspazchick]

Rebuilding the pivots is not an option, unless there is a way to do it automatic. Which I guess there might be, but I don't know it.
Basically the worksheet must be as user friendly as possible, I won't be the only one using it. So the user must be able to copy data from elsewhere, paste it into certain rows, go to another sheet, click a button and wala, results. But I think I will take your advice and run it this evening, then try it again tomorrow and time it. It can take a bit of time but not too long.
I really do appreciate your help, without this forum and all its helpful people, I would be lost.
Michelle

 

[mzspazchick]

Help please...
I entered fresh data and ran the code (clicked the refresh button), as suggested, and it did take some time. Then I came back and entered more fresh data, clicked the button and after two minutes received an error. The following was highlighted in yellow-
.PivotCache.Refresh

and my pivots are either majorly scrambled or practically non-existent. At least I was smart enough to try this on a copy, the original is safely intact, but now I feel like I am back to square one, with a bunch of pivots that scramble upon refresh. Does anyone have any ideas? I would be eternally grateful.
Michelle

 

[mzspazchick]

I have been looking everywhere for help on this. Everything I come across just explains pivot tables but doesn't talk about my problem. I have come across a few codes that might help but I know so little about code, I have no idea what I am looking at.

Does anyone know an easy solution to fixing scrambling pivot tables?
Michelle

 

[ChrisM]

when you enter new data, are you modifying existing rows or adding new rows?

If adding new, are you sure your pivot table ranges are adjusting accordingly?

What happens when you refresh manually?

If a manual refresh works ok, then perhaps your problem is you based all of the pivot tables off of each other and you are refreshing them out of order. I'm just guessing here, not sure if this is true or not.

If this is true, then you could 1) rebuild all pivot tables to reference the data directly or 2) modify your macro code to refresh them in order.

For option 2, your old code:

Sub RefreshData()
'Refresh all pivot tables
ActiveWorkbook.RefreshAll
End Sub

would turn into this:

Sub RefreshData() 
    sheets("sheet1").pivottables(1).pivotcache.refresh
    sheets("sheet2").pivottables(1).pivotcache.refresh
    etc
    etc
End Sub

Make sure you list the pivot tables in the correct "refreshing" order. If you have more than one pivot table on a sheet, you can reference them by pivottables(1) and pivottables(2), or to be more precise right click your table, hit properties, and give it a name, then in code use pivottables("yourname")

 

[mzspazchick]

When I enter new data, I am taking the data from another workbook, copying it and pasting it directly over rows A-G. Once that is done, then refreshing thru a macro. I have had a feeling all along that by creating the seven pivots using Excel's save memory feature that it might be causing a problem so I am presently rebuilding the pivot tables to see what happens.

Thank you ChrisM for your posting, I appreciate your response. I will repost when I am done.
Michelle

 

[mzspazchick]

Ok, I recreated all my pivot tables to be independent and now of course have a "not enough memory error". So I am wondering if I could solve this by having two seperate buttons. One to refresh all pivot tables (the first seven) on a sheet called OutsidePivot, the second to refresh all pivot tables (the second seven) on a sheet called InsidePivot? I tried to modifiy one of the codes given to me but honestly have no clue what I am doing. So I took:
Sub RefreshData()
'Refresh all pivot tables
ActiveWorkbook.RefreshAll
End Sub

and came up with (figured if I could make one work I could do the other next)
Sub RefreshData()
Sheets("OutsidePivot").RefreshAll
End Sub

and got an error. So I took ChrisM's code:
Sub RefreshData()
sheets("sheet1").pivottables(1).pivotcache.refresh
sheets("sheet2").pivottables(1).pivotcache.refresh
etc
etc
End Sub

and turned it into (figured I could put both sheets in this code)
Sub RefreshData()
sheets("OutsidePivot").pivottables.pivotcache.refresh
sheets("InsidePivot").pivottables.pivotcache.refresh
End Sub

Can someone please tell me what I am not getting besides all of it. I would use my Excel help feature on this but unfortunately because I have never used this before, I never installed it and my cd is buried in boxes because I have yet to unpack.
Michelle

 

[mzspazchick]

BUMP

 

[ChrisM]

You took the (1) out of the lines I gave you. Put it back in and make one line for each pivot table on each sheet. You can either number them, which means you'll never be sure which order they are refreshing, or you can refer to them by their pivot table names, which you can set by right clicking anywhere in your table, choosing table options, and entering a name in the name box.

If you want to go the number route, it looks like this:

sheets("sheet1").pivottables(1).pivotcache.refresh
sheets("sheet1").pivottables(2).pivotcache.refresh
sheets("sheet1").pivottables(3).pivotcache.refresh
..
sheets("sheet1").pivottables(7).pivotcache.refresh

If you go the name route, just change this (1) to this ("your name")

 

[mzspazchick]

Sorry ChrisM, I was working on this way too late last night and wasn't thinking. Plus I thought if I just made each worksheet update seperately instead of specific pivot tables that might be enough.
Anyway, I have set it up and attempted to run it and got a run-time error '1004'. "Unable to get the PivotTables property of the worksheet class"
Any ideas?
Here is my code:

Sub RefreshData()
Sheets("OutsidePivot").PivotTables(OutSumcontractpay).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutSumContract).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutAvPay).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutSumValidOrders).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutSumPDC).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutSumNormNew).PivotCache.Refresh
Sheets("OutsidePivot").PivotTables(OutSumNewIncrease).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumcontractPay).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumcontract).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumAvPay).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumValidOrders).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumPDC).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumNormNew).PivotCache.Refresh
Sheets("InsidePivot").PivotTables(InSumNewIncrease).PivotCache.Refresh
End Sub

Michelle

 

[ChrisM]

need quotes around your pivot table names - ("pivot name")