Automate a filter lookup to copy the results using a macro 1

[rcbatlinux]

I am trying to automate a filter lookup and then copy the results using a macro. The problem I'm having is specifying the criteria. I want the criteria lookup be based on the value of a field. Here is what I have:

Range("B1&quot.Select
Selection.Copy
Sheets("Team Data&quot.Select
Range("L2&quot.Select
ActiveSheet.Paste
Range("A1&quot.Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=


If I make the Criteria1:= a literal it works fine. But I want it to look at the value of a cell. I tried entering in the cell address and the range command but neither worked. Any ideas?

Rick

 

[Dreamboat]

Rick, should you be using saved view instead? Then have your macro go get the view and then edit-go to cells that have <whatever> and copy.
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[rcbatlinux]

Thanks for the reply. I dug a little deeper and realized I could just use the Advanced Filter option which allows me to specify a criteria from a cell address. thanks.

Rick

 

[JCRMet]

rcbatlinux - I need to use the advancedfilter in VBA - can you provide the code - I tried using a it but can't get the criteria piece to function correctly.

 

[rcbatlinux]

JCRMet,

I created the code by making a separate macro and copying and pasting the code into the macro I was going to run. I used the advanced filter option. Below is the code:

Range(&quot;A1:J7&quot.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets(&quot;Sheet1&quot.Range(&quot;A1:A2&quot, Unique:=False

Hope this helps.

Regards,

rcbatlinux