Hi folks,

This is my first post here but just a quick thanks for the invaluable info contained within this forum.

Here's my questions... I'm quite new to Excel (beyond basic functions that is) and I want a table and a pivot chart to to filter via the contents of a cell. This actually works fine in separate sheets however I need both macros to function in one sheet. Here's the 2 codes I'm using...

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "c3"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Range("O18:Q18").AutoFilter Field:=1, Criteria1:=Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub



and....


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B16")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets("Sheet 2").PivotTables("greg").PivotCache.Refresh
With Me.PivotTables("greg")
.PivotCache.Refresh
.PivotFields("material").CurrentPage = Target.Value
End With
Application.EnableEvents = True
End Sub


Could anybody tell me how to make both of these work within one sheet?

Alternatively, I've tried using the formula that displays the contents of a cell in the sheet with the pivot chart and then linked the target range to the resulting formula however because it is formula the pivot chart doesn't recognise it. I guess it might be a simple case of editing the code on the pivot macro so that it takes the target cell from the other sheet however I haven't been successful in this.

Thanks in advance for your help.

Greg