Updating Pivot Tables in Excel I'm writing some code for someone to update a pivot table field based on the contents entered in a particular cell.

I am unfamiliar with VBA and programming for excel but I followed some tutorials and would like if someone here could look over what I've done just to see if everything looks ok before I give it to them as I don't have access to their excel worksheet.

Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Application.Range(B8)) _
    Is Nothing Then
        UpdatePivotFieldFromRange _
        B8, B3, Outfield
End If
End Sub


Public Sub UpdatePivotFieldFromRange(RangeName As String, Fieldname As       String, PivotTableName As String)

Dim rng As Range
Set rng = Application.Range(RangeName)

Dim pt As PivotTable
Dim Sheet As Worksheet

For Each Sheet In Application.ActiveWorkbook.Worksheets
    On Error Resume Next
    Set pt = Sheet.PivotTables(PivotTableName)
Next
If pt Is Nothing Then GoTo Ex
On Error GoTo Ex

pt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim Field As PivotField
Set Field = pt.PivotFields(Fieldname)
Field.ClearAllFilters
Field.EnableItemSelection = False
SelectPivotItem Field, rng.Text
pt.RefreshTable

Ex:
pt.ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Public Sub SelectPivotTableItem(Field As PivotField, ItemName As String)
Dim Item As PivotItem
For Each Item In Field.PivotItems
    Item.Visible = (Item.Caption = ItemName)
Next
End Sub
So, let's start with the Worksheet event and what you're trying to do. You only want to trigger the pivot update if the changed cell is B8. There's no need to intersect, just check the target's address. 

Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "B8"
            UpdatePivotFieldFromRange "B8", "B3", Outfield
    End If
End Sub


Note that I properly indented the code and got rid of that horrid line continuation. 



Now, we can move onto UpdatePivotFieldFromRange. Kinda...

You're immediately recreating the target range at the beginning of this sub, so just pass Target in as a range instead of a string. That changes the event to this:

Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "B8"
            UpdatePivotFieldFromRange Target, "B3", Outfield
    End If
End Sub


And your declaration of UpdatePivotFieldFromRange to this:

Public Sub UpdatePivotFieldFromRange(rng As Range, Fieldname As String, PivotTableName As String)


But that's still not right. There's no reason for this to be public, declare it private instead. I also don't like the name rng. It's not meaningful, but I'll leave it to you to give it a meaningful name. 

There is so much wrong here I'm not sure where to start. 


For Each Sheet In Application.ActiveWorkbook.Worksheets
    On Error Resume Next
    Set pt = Sheet.PivotTables(PivotTableName)
Next
If pt Is Nothing Then GoTo Ex




There's no reason to call Application. ActiveWorkbook.Worksheets works just fine. 
But you're begging for bugs. If the user clicks on another workbook while this is running, the sub is suddenly working on a different workbook. Use a Workbook typed variable instead. 

Dim book As Workbook
Set book = ActiveWorkbook

For Each sheet In book.Worksheets

The next thing to note is you're setting On Error GoTo Next at each iteration of the loop. It should be moved outside of the loop. 
But, you shouldn't ever use On Error GoTo Next. Seriously, Never do this. You're telling vba to simply swallow the error. It's analogous to having an empty catch block. Find a way to check to see if the pivot table exists instead. 
Finally, you're using GoTo to jump to your error handler. You should also never do this. It's much better to Raise an Error. 


That said, I do like that you're always ensuring that ScreenUpdating gets turned back on. (Well, you would be, if you set OnError GoTo Ex directly upon entering the subroutine.)



Again, there's no reason for a private function to be declared Public. Look into scoping and declaration in vba. It's important. If these were functions instead of Subs, they would be available as user formulas. 

Secondly, do you really intend for this to hide every field except the one you're looking for? I doubt it. 


Public Sub SelectPivotTableItem(Field As PivotField, ItemName As String)
Dim Item As PivotItem
For Each Item In Field.PivotItems
    Item.Visible = (Item.Caption = ItemName)
Next
End Sub



I have a feeling that what you really meant to do was turn the particular field on/off like this. 

Public Sub SelectPivotTableItem(Field As PivotField, ItemName As String)
    Dim Item As PivotItem
    For Each Item In Field.PivotItems
        If Item.Caption = ItemName Then
            Item.Visible = True
        End If
    Next
End Sub