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

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

"Becky" wrote:

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

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.

"bj" wrote:

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

"Becky" wrote:

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

Becky,

I know what u're saying. I was looking for the same thing. Pre-select your cell of interest and then try this:

Dim text1 As String
text1 = ActiveCell.Value2

Sheets("XXX").Select
ActiveSheet.Range("$A$1:$S$1000").AutoFilter Field:=6, Criteria1:=text1


Replace items in bold with whatever suits you.