Dynamic Auto filtering based on criteria

[wannabeMVP]

Scenario: Each division has its own worksheet showing a table of sales
for Product code-by-Region (Product codes L-R along columns, regions
top-down across rows with the # of units sold in any given cell).

There's a master worksheet which lists the various products with
columns for "division name", "product code" and "region". There are
other values listed as well such as Product ID, product code, sale$,
profit$, salesperson$, date product was introduced etc.

E.g.
1001 BLEN Div1 1/1/2002 Eastern-U.S. 2002-1 $50.00 $20.00 $30.00 Jack
1002 WMAC Div1 1/1/2002 Eastern-U.S. 2002-1 $400.00 $100.00 $300.00 Mike
1001 BLEN Div3 1/1/2002 Eastern-U.S. 2002-1 $50.00 $20.00 $300.00 Mike
1003 DRYR Div2 1/1/2002 Eastern-U.S. 2002-1 $500.00 $100.00 $300.00 Mike
1003 DRYR Div1 1/1/2002 Eastern-U.S. 2002-1 $500.00 $100.00 $300.00 Mike

What I would like to do is that when the user clicks in a given cell
on the main table in any division's worksheet (e.g. Div1), the macro
captures the three variables:
(a) Division name:
(b) Product Code
(c) Region

and then either:
1. lists all the entries in the master worksheet which satisfy these
three criteria and paste them into another worksheet, or
2. Use these three criteria to auto-filter the master worksheet.

[wannabeMVP]

I have put together some code to get this done.
The problem is that nothing comes up with this if I don't hard code the values.
(if I hard code then the filtering works out perfect!)

It seems there's some problem with capturing the values of the offset cells.

Sub Button6_Click()
Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String, Crit1 As String, CritR As String, CritC As String

Crit1 = "Div1"
CritR = ActiveCell.Offset(ActiveCell.Row - 16, 0).Value 'Row 16 has prod. code
CritC = ActiveCell.Offset(0, ActiveCell.Column - 1).Value 'First Col has intro. qtr.

Sheets("Product Sales Data").Select
Set w = Worksheets("Product Sales Data")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With

w.AutoFilterMode = False
w.Range("C6").AutoFilter field:=3, Criteria1:=Crit1 'filters by Division Name
w.Range("B6").AutoFilter field:=2, Criteria1:=CritR 'filters by prd code e.g. WMAC
w.Range("L6").AutoFilter field:=12, Criteria1:=CritC 'filters by intro qtr. e.g. 2004-1

End Sub

[wannabeMVP]

God, am I dumb sometimes...............

I wanted to offset towards the top (for row) and to the left (for col).

That means NEGATIVE offsets!!

It works now.

CritR = ActiveCell.Offset(16 - ActiveCell.Row, 0).Value
CritC = ActiveCell.Offset(0, 1 - ActiveCell.Column).Value