AutoFilter macro with dynamic criteria

[juuck]

Hello

I noticed thet here ppl get a lotta help with VBA and excel issues. I hope you guys can help me too

Heres my problem (Im using Excel 2007)

Im using a cell value as a filter criteria in another sheet.

Heres my schema:

Sheet1: Data (col1, col2, col3...col10)
Sheet2: Control Values (control_col1, control_col2, control_col3....control_col7)

What I do is runing a macro that AutoFilter data in Sheet1, according to the actual value for the column. For example, I filter the data with col7 = sheet2.control_col7 (col7 is a date value), delete the records that match this criteria, and then replace the control_col3 value with the next date.
The idea of the macro is to delete the history of certain data, and update the control_col7 for the next time the macro runs.

The macro works fine the 1st time, but the 2nd time, something happens to the criteria.... i doestn filter correctly... i have to enter manually to the field, and re-apply the filter (without changing the criteria) and then it filters correctly.

Heres the code I'm using

Please Login or Register  to view this content.

here is where I assign the criteria value

Please Login or Register  to view this content.

Hope anyone can help me...

[davesexcel]

Maybe attach a sample workbook showing the desired results as well, save it as a pre-2007 format.