In MS EXCEL on a list with Autofilter turned on...
I have a list and using the autofilter i can show particular sets of the data which i print by selecting one element ( which shows up the corresponding data )on to one page and with the next element selected (along with its corresponding data) on to another page and so on....

Now this is repetetive and tedious. i want to be able to print the complete data at once and get the output on multiple pages....on each page of hard copy only one element and its data to appear.

how can i automate it so my clerk can do it on his own quickly and easily?

many thanks
abhinav

Hi Abhinav,

Some VBA would probably be very suitable.... Without VBA by using a pivot table, if it suits your requirements - there are two pivot table approaches.

1. You set the field for which you want each page as a pivot table "page field". Then via the pivot table toolbar use "Show Pages" to 'instantly' create one new worksheet for each element of the page field. Then print these worksheets.

2. Set the field as the first row field and change its field setting - layout, the print option "insert page break after each item". When you print, each element starts on a new page.

HTH
Fazza

hi thanks any help for the VB method?
How do i capture the all the current values in the autofilter dropdown list of a particular field and print out a page for each of them through vb code?


i am attaching a test file with some test data...


i need to print a page for each element in the "Depot/Unit" field. where:-
"RV No" field is blank and "Delay in No Of Days" Field is more than the value in Cell F4

Hi Abhi,

Your code might be something like

• check autofilter is on, with no filters in place. Maybe save initial settings
• filter one column for delay > range ("F4").value and one column for blank in "RV No"
• check if there are any rows visible, if not, reset autofilter to initial setting and exit sub
• for the list of items showing, create a list of the unique items. Suggest into an array, but you could copy & paste the visible cells to a temporary worksheet location and use the advanced filter. Or use a collection
• loop through these unique entries one at a time, and for each one set the autofilter on the "Depot/Units" field and print the report
• delete temporary range/s if used, reset file to initial set up

The macro recorder will help with some of the coding & syntax. Setting page setups can be very slow, so suggest you do that once at the beginning, or, if appropriate, set it outside VBA and have it correctly set all the time.

If you want any help on any details, please post the code you have.

regards,
Fazza

Abhi,

I just thought, the reporting you want is basic from a database.

It would really suit linking your Excel data to an MS Access table and reporting from that. Setting the Access report to start each section on a new page is trivial. I think it would be the right tool for the job. Also, it is trivial to handle the criteria of blank in one field and greater than some value in another.

HTH,
Fazza

yes fazza...the access thing would be simpler i think too......
ill try in the mean time can you do the vb in the excel test file and post it so i can have a look in that route too?

Abhi, I'd rather not, thanks. I am happy though to help you with any steps you get stuck on. Fazza