Matching a cell ref. in a macro

[tuph]

I am just starting out with VBA and copied some macro code (copied from a post on this forum) that filters data and copy/pastes the result into another worksheet. As written and modified for my workbook it works really well, but I have some things I wish to change but can't work out how.

I am attaching a sample of the workbook (with names deleted).

1) I can only make the macro work if I hard code the criteria, but I want to reference cells on the Data worksheet.

2) I don't want to delete and replace the MyAutoFilter worksheet every time, but prefer to paste the results into the Summary worksheet starting in cell A10 and leaving one blank row between each resulting data set. (There are 3 separate pastes in the macro.)

This is the section of code I need to modify to reference a particular cell. Except for the capitalized comment, all other comment rows are from the original macro author. (I tried using Criteria1:="=" & WS.Range("D4").Value but got an error on running the macro.)

Please Login or Register  to view this content.

I can see lots of benefits in learning about macro VBA and would really appreciate any help I can get with this.

[Leith Ross]

Hello Tuph,

The "=" and "<>" strings have special meaning for the criteria arguments. The equal sign "=" tells the AutoFilter to find all Blank Fields, and the not equal "<>" to find all Non-Blank Fields. Remove the "=" and "<>" strings from your Criteria1 assigments.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[tuph]

Hello Tuph,

The "=" and "<>" strings have special meaning for the criteria arguments. The equal sign "=" tells the AutoFilter to find all Blank Fields, and the not equal "<>" to find all Non-Blank Fields. Remove the "=" and "<>" strings from your Criteria1 assigments.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks Leith! I've done that and the macro runs okay but doesn't give me the results I need. Can I say <= the value of the referenced cell? My code now looks like this:

Please Login or Register  to view this content.

with a value of -10% in D5. The actual calculation needs to return rows with a value less than or equal to -0.1 in column 2. I need the code to be something like:
Criteria1:<=WS.Range("D5").Value. (I tried this, of course, but it gives me a compile error.)

Cheers,

[tuph]

Thanks Leith! I've done that and the macro runs okay but doesn't give me the results I need. Can I say <= the value of the referenced cell? My code now looks like this:

Please Login or Register  to view this content.

with a value of -10% in D5. The actual calculation needs to return rows with a value less than or equal to -0.1 in column 2. I need the code to be something like:
Criteria1:<=WS.Range("D5").Value. (I tried this, of course, but it gives me a compile error.)

Cheers,

Leith,

With some experimentation and internet research I managed to work this out.

Thanks again for your help!