Macro using cell value

[bruce21]

Hello all,

I need help on this please. I need to use the value from cell (B5)'sheet1' in a filter on sheet2.

When I record my macro I am copying and pasting the value of B5 over to sheet2 filter and then copying and pasting the results back over to sheet1. This is working but the problem is that I can't reset the value from the previous selection. I've tried using the clear filter button in the macro but the old value is still there. See code below.

Would the Criteria1: be equal to cell b5 and not Ace Hardware? How would I code that?

Thanks to all!
BD

Range("B5").Select
    Selection.Copy
    Sheets("Account Track").Select
    ActiveSheet.Range("$A$1:$AF$357").AutoFilter Field:=1, Criteria1:= _
        "=*Ace Hardware*", Operator:=xlAnd
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A17").Select
    ActiveSheet.Paste
    Range("A17").Select
    Sheets("Account Track").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Sheets("Sheet1").Select

 

[Fluff]

What exactly is in B5?

 

[bruce21]

Cell B5 is a drop down list to select different customer names that I want to filter on in the Account Track sheet.

Thanks.

 

[Fluff]

Will the names in B5 exactly match the names in the table?

 

[bruce21]

Yes

 

[Fluff]

In that case you can use

 ActiveSheet.Range("$A$1:$AF$357").AutoFilter Field:=1, Criteria1:= _
        [COLOR=#0000ff]Sheets("Sheet1").Range("B5").Value[/COLOR]

 

[bruce21]

Yes works perfect. Thanks Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback