Macro to filter a list

[excelnovice1979]

Hello everyone,

I'm hoping that all you excel genius's out there canhelp me out!

I'm ok with excel but have little or no experience with macros & VBA, so you'll need to explain it to me slowly!

I have a table of data that i need to filter to produce monthly reports, however i need to be able to change the filter criteria each month.

I currently copy and paste the table from access, then i set about using the filter option on various column headings to generate about 4 or 5 different reports.

I was hoping i could record a macro to do all this for me, the filtering, but i'm not sure how to have it let me change the filter?

I recorded a macro for the filter and it worked, but of course it is then set to that one criteria.

Can anyone help me?

Oh yeah, i'm using excel 2010

Thanks!!

 

[pboltonchina]

I used this to change font colours of numbers, I'm sure you could change it to suit your needs

Range("A1").Select
     With Selection
    Columns("A:A").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=".1"
    Selection.Font.Bold = True
    Selection.AutoFilter Field:=1, Criteria1:="..2"
    Selection.Font.ColorIndex = 3
    Selection.AutoFilter Field:=1, Criteria1:="...3"
    Selection.Font.ColorIndex = 5
    Selection.AutoFilter Field:=1, Criteria1:="....4"
    Selection.Font.ColorIndex = 4
    Selection.AutoFilter Field:=1, Criteria1:=".....5"
    Selection.Font.ColorIndex = 8
    Selection.AutoFilter Field:=1, Criteria1:="......6"
    Selection.Font.ColorIndex = 7
    Selection.AutoFilter
    End With

 

[Peter_SSs]

Welcome to the MrExcel board!

Perhaps you could show us the recorded macro (see my signature block about using Code Tags) and explain which bits have to change and how/when it is decided what to change them to.

 

[excelnovice1979]

Thanks for the replies, i saw the code but when you say i could adapt it i have no idea what the code currently does and i have no idea what i would need to change or how?

As for posting the macro i have recorded, i seem to have lost it now-not sure how i did that but i can't find it!

Perhaps it would help if i described in more detail what i need to do and hopefully someone will be able to help me with a solution.

I work in our accounts department and every month i run about 4 reports. I have been copying and pasting details from our database on Access, into excel and then putting filters on every column and using various combinations of filtering information, sorting the list and hiding irrelevant columns to get it into the appropriate report and format.

The column headers are something like:
Client Ref, Client Name, Accounts year, Accounts month, Accounts day, Filing deadline, Info received, work started, passed for review, sent to client

The 1st report i run is to show the clients whose accounts are the current month, so normally i use the drop down filter and de-select all months and then select the current month.

So i was hoping i could have a macro where you select the month or maybe type it in a cell and the macro would then do the filtering based on either the cell or some selection the user can make.

The next report is to check on the filing deadlines, first i sort the table by the filing deadline from oldest to newest, again from the drop down filter options. I then filter the deadline dates so that the list only shows old deadlines and those up coming in the next 2 months. Anything further ahead is ommitted. I currently use the drop down filter to de-select the months not required. So clearly every month the deadlines to be filtered out will change. So as above i am hoping to create a macro that will do the sort and then give me an option of what deadline dates to filter out of the list.

I also look at work that is in progress, to do this i might filter various columns to look at specific jobs. I might look at accounts that have been sent to clients but not returned. I would filter the column for accounts sent to client to exclude blanks so that only jobs that have been sent to the client are displayed. I would then filter the column for accounts return from client to only show blanks e.g. those not returned.

So i think that about covers the general idea of what i am trying to do. I hope that with all your help i can reduce this task to the press of a few buttons and selecting a few different options.

Thank you all for any replies. I'm afraid i'm really going to need some help in doing this, the previous post with some code was more than i am able to deal with at present.

Thanks!!

 

[Norie]

I know this probably isn't what you want to hear but wouldn't this be easier if you kept the data in Access?

It's kind of built for this sort of thing.

For example the 1st report could be done with a simple query using Month(Date) as the criteria for the months field/column.

The query could then be used as the record source for a report, or if you really need it in Excel you can export the results of the query to a worksheet.

Similarly the 2nd report can be down with criteria like this,

Not [FilingDeadline]>DateSerial(Year(Date), Month(Date)+2, Year(Date))

against the Filing Deadline.

These are pretty straightforward queries for Access, not any code in sight.