Macro or Pivot problem?

mzspazchick

New Member
Joined
Nov 10, 2005
Messages
39
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mzspazchick

New Member
Joined
Nov 10, 2005
Messages
39
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Nov 10, 2005
Messages
39

ADVERTISEMENT

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
:biggrin:
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Nov 10, 2005
Messages
39

ADVERTISEMENT

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!!!

(y)
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Nov 10, 2005
Messages
39
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,012
Members
412,353
Latest member
SofiaV
Top