Refreshing Pivot Tables

  • Thread starter Thread starter LoriM
  • Start date Start date
L

LoriM

I am creating pivot tables for 10 different people, all with the same
fields but different data. Instead of creating the pivot table 10
times, I want to use the previous one and just "copy" the new
information into it. Clicking on refresh puts the new info in the
cells, but the item display (filter) keeps the previous information
along with the new. I don't want these guys to see everyone else's
data. (Not sure of the terminology of the display - this is where you
can check which ones you want to show up.)

Any help would be greatly appreciated, as I was supposed to have this
done last week!
Thank you!
 
To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
'================================
 
To store the macro code:
1. Hold the Alt key and press F11, to open the Visual Basic Editor.
2. In the Project Explorer, at the left, find and select your workbook
3. Choose Insert>Module
4. Copy the code from my previous message, and paste it onto
the module sheet.
5. Close the Visual Basic Editor and return to Excel

To run the macro:
1. Choose Tools>Macro>Macros
2. From the list of macros, choose DeleteMissingItems2002 (if you have
Excel 2002) or DeleteOldItemsWB (if you have an older version)
3. Click Run.

Create a button to run the macro
1. Right-click any toolbar at the top of the worksheet.
2. From the list of toolbars, choose Forms
3. Use the Command button tool to add a button to the worksheet.
4. The Assign Macro dialog box should open.
5. Choose your macro, then click OK.
6. Close the Forms toolbar.
 
Back
Top