Customising Autofilter using Macros 1

[Navvy]

Hiya, I am using an autofilter which list for example a whole list of dates (for a date column). However, suppose I want to find out just last weeks data I can put the following information in the Custom Autofilter table (given today is 03/27/02):

equals: 27-Mar-02
and
is greater than or equal 20-Mar-02

Is there anyway for me to automate this, i.e have a script that automatically finds todays date - 7 days for a week?

Thank you.

 

[xlbo]

Well, to find today's date just use =format(Now(),"dd/mm/yy&quot and =format(Now()-7,"dd/mm/yy&quot will give you 2 dates a week apart
HTH
Geoff

 

[DaleWatson123321]

Navvy,

A natural extension of "Autofilter", is the "Advanced Filter", and its related "database functionality".

If you, or anyone else would like, I can email you an example file which permits the user to specify the "number of days" (back from the current date) into an "input cell", and then click on a "macro button" which extracts those records to a separate sheet where the data is prepared for printing.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Navvy]

Thanks for the quick response!

What I'm looking for, is to press a button that I'll put on the sheet which automatically gives me data for the last week. How would I code this?

 

[SkipVought]

Hey, Navvy,

You have been nosin' around Tek-Tips now and again for several months, as I recall. You really ought to become a Tek-Tip member and fully participate.

You, no doubt, have received a number of good tips, yet are not able to respond with your appreciation in the way that a member is able. Furthermore, such responses identifie to other participants, postings worthy of review, so you are denying others that benefit.

Now for your question.

=INT(C12/7)*7

will give calculate to a week (adjust for week-beginning from here)

Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Dale, thank you for your help. You method and technique is remarkable. Once again, thank you for you time and efforts. This star is very well deserved. Sorry for the delay!