PDA

View Full Version : Pivot Table Filter works in 2007, it doesn't in 2003



niebyl2002
04-13-2012, 08:37 AM
Hello Everyone,

I have a worksheet that I had to convert to excel 2003 and part of the code doesn't work anymore. Not sure what's the problem. Here's the brief descritpion what worksheet does:

VBA Forms opens up with workbook, user picks up rep name (combo box with preloaded list) and macro filters all pivot tables with specified criteria. Code works perfectly in 2007. Forgot to mention I'm filtering row columns in Pivot Table.

Sheets("Unconfirmed").Select
Set pt = Sheets("Unconfirmed").PivotTables("PivotTable2")
'Manual Update - ON (speeds up code run)
pt.ManualUpdate = True
'Error Bypass
On Error Resume Next
'Loop PivotItems
For Each pi In pt.PivotFields(" Buyer").PivotItems
pi.Visible = pi.Value = frmReportMenu.cboBuyerName.Text
Next pi
On Error GoTo 0
'Manual Update - OFF
pt.ManualUpdate = False

Funny thing is it I'm using this code to clear all filters and it works just fine, see below

'Unconfirmed Pivot Table - OK
Sheets("Unconfirmed").Select

Set pt = Sheets("Unconfirmed").PivotTables("PivotTable2")
'Manual Update - ON (speeds up code run)
pt.ManualUpdate = True
'Error Bypass
On Error Resume Next
'Loop PivotItems
For Each pi In pt.PivotFields(" Buyer").PivotItems
pi.Visible = True
Next pi
On Error GoTo 0
'Manual Update - OFF
pt.ManualUpdate = False

I used another code for filtering Report Filter on Pivot Table but it doesn't work on rows. See below

'Summary Pivot Table - OK
Sheets("Summary").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields(" Buyer").CurrentPage =
_ frmReportMenu.cboBuyerName.Text

Sorry for a long thread but I have no idea what's wrong with my code.

I would really appreciate any feedback. Thank you