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]

Would it matter if when I created my pivots, I selected "use same data as... say my first pivot I created (the memory saving feature). Or would selecting to keep the data independant for each pivot table be better.

Or would none of it matter to my problem.
Any ideas???

I am presently changing my column heading names to more unique names and redoing my pivots but this is very time consuming.
Michelle

 

[mzspazchick]

BUMP

 

[Datsmart]

You have found why I avoid Pivot tables as much as possible.
I got this code below from this site some time back, seems to clean up Pivot tables after they have been reused time and again. It may take a little time to complete the first time. Be sure to save your WorkBook before using it.

Hope it helps.

Sub UpdatePivots()
Application.ScreenUpdating = False
' This function updates all pivottables in the workbook,
' recalculates and removes old data
Dim ws As Worksheet
Dim ip As Long
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Updating " + ws.Name + "..."
For ip = 1 To ws.PivotTables.Count
With ws.PivotTables(ip)
On Error Resume Next
For Each aPF In .PivotFields
For Each aPI In aPF.PivotItems
aPI.Delete
Next aPI
Next aPF
On Error GoTo 0
.PivotCache.Refresh
.RefreshTable
End With
Next
Calculate
Next ws
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Don't remember who to thank for the code.

 

[mzspazchick]

You Said "Don't remember who to thank for the code."

Well thank you Datsmart, I will give it a shot. Without the pivots I don't think I would be able to organize the data I am organizing.

Now a couple quick questions about your code:
Where do I enter it? I have only entered one small code that refreshes my pivots upon the click of a button. And, can I copy, paste everything you put, or will I need to type it in?
And lastly, Is the following part of the code?
' This function updates all pivottables in the workbook,
' recalculates and removes old data

Thanks a bunch,
Michelle

 

[Datsmart]

You can paste the code in the same Module as the code for your Macro button.
Richt Click your Macro button,
choose "Assign Macro",
choose "Edit"
In a blank area below your existing Macro code, paste the code I sent you.

The two lines you mention are comments in the Macro. Anything preceeded by a single quote does not process. It is there to remind you what the code is for.

Good luck.

Datsmart

 

[mzspazchick]

One more question:
My macro reads:

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

prior to your code. It looks like yours does what mine does, only better, should I replace mine with yours? Instead of putting your below mine?
Michelle
Again...Thank You!!!

 

[Datsmart]

If it runs fast enough for you go ahead and replace it.
I had a Pivot Table that it took several minutes to run on, so I only used it when needed.

The code not only refreshed the Pivot, but removes unused data.

Did it clear up your "Scrambled data" issue?

 

[mzspazchick]

Wow!! You weren't kidding about taking awhile. I force quit it after 15 minutes. So I don't know yet if it works. But thank you for taking so much time to help me out on this issue.
When you say you only used in once in a while, what do you mean? I noticed that when I put both codes in, I have the option of which to assign to the button. If you don't use it all the time, won't it scramble everything sometimes. When I used my simple code, it scrambled the second time I used it.
Michelle

 

[Datsmart]

I only ran it after making a lot of changes to the data the Pivot table refered to. I would recommend finding a time you can let the macro run until completion. With 7 pivot tables this will take some time, but it will run quicker after that.

Another option is to Delete each of the Pivot tables and then rebuild them.