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
Bookmarks