Macro Help in Pivot

[Eddy Stan]

Hi all,

I get run time error '1004', unable to get the pivot tables property of the
worksheet class...
please check the code below... I get biz name at B1 and region at B2
Now i want the pivot to show only biz type in b1 cell and region at b2 cell
I want to protect pivot and data sheet, dont want manager to select thro
pivot table.
Is it possible.
can you please fix the error in the code below

Sub Show_item_of_oneField()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPromptPF As String
Dim strPromptPI As String
Dim strPF As String
Dim strPI As String

' strPromptPF = "Please enter Field Name to filter"
' strPromptPI = "Please enter Item Name to filter"
'strPF = InputBox(strPromptPF, "Field Name")
'strPI = InputBox(strPromptPI, "Item Name")
strPF = Range("b1").Value
strPI = Range("b2").Value

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(strPF)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If pt.RowFields = strPF Then
pt.RowFields = True
Else
pt.RowFields = False
End If

If .PivotItems = strPI Then
pi.Visible = True
MsgBox pi
Else
pi.Visible = False
MsgBox pi
End If
Next pi
.PivotItems(strPI).Visible = True
.AutoSort xlAscending, .SourceName
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I need the pivot to be refreshed on click of buttom (having macro).
the important thing is that the user (manager) selects "Biz type" at b1
(validation list); selects "Region" at C1 (validation list)

I want the pivot to show that biz & that region selected
-show Biz type, Region, customer, bil amount, tax1 & tax2
suppress biztype total & region total
show only customer total (bill amount, tax1, tax2)

Eddy Stan
let us come to the below after the above is sorted.
*----------------------------------------------------------
3 more pivots are related to results of this pivot,
like pivot table2:
Customer wise No.of sales, value, Noof sales return, value
for the region & biz type selected in pivot table1
a Graph is attached to pivot table2
*-----------------------------------------------------------

 

[Patrick Molloy]

this can't be correct:

For Each pi In pf.PivotItems
If pt.RowFields = strPF Then
pt.RowFields = True
Else
pt.RowFields = False
End If

you're looping thro pivot items but checking pt rowfields then trying to set
the same fiedl to true or false? What are you trying to do?

 

[Eddy Stan]

I know, that is why i have come to forum..
pl read fully.. you will see what i want. I have explain more than required.
edit my code and show how i can view my pivot depending on input in B1 & C1