Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1

    Question Unanswered: Printing on autofilter values

    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
    Last edited by abhichoudhary; 10-25-06 at 13:30. Reason: rephrasing question for better understanding
    Abhi
    A2K3 WinXP Prof SelfLearner

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    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

  3. #3
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1
    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
    Attached Files Attached Files
    Abhi
    A2K3 WinXP Prof SelfLearner

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    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

  5. #5
    Join Date
    Feb 2006
    Posts
    113

    a better way?

    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

  6. #6
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1
    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
    A2K3 WinXP Prof SelfLearner

  7. #7
    Join Date
    Feb 2006
    Posts
    113
    Abhi, I'd rather not, thanks. I am happy though to help you with any steps you get stuck on. Fazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •