Macro - select cell value, not permanent value

[Guest]

Per the macro below, how do we select the cell value and not the number value
currently in the cell? That is, we don't want it to select "2871". We want it
to select what is in cell A1. If it happens to be 2871, great. If not, we
want it to display whatever number is there.


Range("C7").Select
Selection.Copy
ActiveSheet.Next.Select
Selection.AutoFilter Field:=1, Criteria1:="2871", Operator:=xlAnd
Range("A1").Select
End Sub

 

[Guest]

not a hundred percent sure I know what you want but try
ActiveSheet.Next.Select
cri1 = range("A1")

Selection.AutoFilter Field:=1, Criteria1:=cri1, Operator:=xlAnd
Range("A1").Select

 

[Guest]

That didn't seem to work. Let me try to rephrase.

We have two sheets. On sheet 1, in cell A1, we have a product # (e.g.,
4328). On sheet 2, starting in cell A1 going down, we have a data set that
includes many different product numbers (including e.g., 4328) along with
other data.

The macro should do this: Using the number in cell A1 on Sheet 1, filter on
cell A1 on Sheet 2 for that number. If we have three product numbers in the
dataset, it should only display the number that was filtered.

Hopefully this makes more sense.