How to update Page field using result in another cell from drop down?

I am new to macros and VB with excel. I need to have a pivot tables page filed updated using a reference to another cell that is populated via a drop down.

I have pulled several examples from the net, however i am unable to get them working.

The one example I have is below. I have copied the code and replced all values, yet i still feel i am missing something?

Please help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Calling Number"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    If Target.Address = Range("C2").Address Then
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Hi,
Please wrap your code with the # icon...

What happens when the code activates?

Originally Posted by spmorin

Sorry about not wrapping code, first post.

When I activate the code nothing happens, or that is what appears to happen. The example spreadsheet i pulled this from works fine, however, when i try to copy to my spreadsheet I have no luck? I have even attempted to use the "debugger" to see if i can determine what is happening, but i simply do not have enough experience.

Where did you paste the code??
check this out to see where the worksheet module is, as the code you present is a worksheet module code....


http://www.contextures.com/xlvba01.html#Worksheet

Originally Posted by spmorin

Thanks for the reply. This is the site I have been using for most of my samples and such. I did copy as the examples states. I am now able to get the code to work if I was the target cell with the validation, how ever if I use the target cell with a vlookup, it will not work.

I wish to use the vlookup as I am using two different sheets for the data, however I can combine, just takes abit more work.

any other input appreciated.

Thanks

Maybe worksheet_calculate would work then........