Refreshing pivot table adds records

[kbear]

Excel 2003 using SQL tables: We have a pivot table where the first column is the expense report number. The user selects the reports she wants to process. Then refreshes the pivot table to ensure the amounts are most current. When she does that, added to the column are new expense reports and they are checked by default. I have talked through using page fields that would limit what's returned but nothing is appropriate. Is there a way around this? Thanks.

 

[SkipVought]

Hi,

"The user selects the reports she wants to process. "

How is the selection done?

Why not a Page Field?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[kbear]

She uses the drop-down for that field and checks the ones she wants. When the data is refreshed, all the new reports are checked. These are reports that she has audited and wants to pass the list off to another person. She may work on the list one day and print it the next and She wants to ensure that any changes are captured.

A page field wouldn't work because you can't select multiple items from a list - unless there's a way I've never seen.

 

[SkipVought]

"When the data is refreshed, all the new reports are checked. "

Refresh the PT, before the user makes a selection.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[SkipVought]

Something else you might consider is a USER PROFILE LIST.

Assuming that there are multiple users, each having a different mix of expense report numbers, or even if its ONE person, have a list.

Then macro record the process of cleasing the selections in the PT and making the resuired selections.

Then the macro can be modified quite easliy to reflect the selection in the list.

If you care to pursue this option, post back in Forum707 along with your recorded code and a description of your table.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[kbear]

IF it were that easy...The audit is done over time - a day or days. If she has been working on five reports, she only wants those five to be updated. So refreshing just before she hands those five over is going to pull in new expense reports where all she wants is those five updated.

 

[SkipVought]

Here's a freebie...

Sub SetItems()
'run this code
'it assumes that this is the FIRST pivot table
' and that the  expense report numbers are the FIRST row field
' and that the user prifile table has a NAMED RANGE, named  expense_report_numbers on a sheet named UserProfile
'==========================
    Dim pit As PivotItem, r As Range, bFound As Boolean
    
    Application.ScreenUpdating = False
    
    ShowAll
    
    For Each pit In ActiveSheet.PivotTables(1).PivotFields(1).PivotItems
        bFound = False
        For Each r In Sheets("UserProfile").[expense_report_numbers]
            If r.Value = pit.Value Then
                bFound = True
                Exit For
            End If
        Next
        pit.Visible = bFound
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Sub ShowAll()
'called from SetItems
    Dim pit As PivotItem
    For Each pit In ActiveSheet.PivotTables(1).PivotFields(1).PivotItems
        pit.Visible = True
    Next
End Sub

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[kbear]

Wonderful. I will give it a try. Thank you.