Results 1 to 5 of 5

Thread: VBA Code to Print Multiple Worksheets with Filtering

  1. #1

    VBA Code to Print Multiple Worksheets with Filtering



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone! Here is what I am looking to do: I have an Excel Workbook with 10 Worksheets in it named "P1", "P2", thru "P10". Each worksheet has the same format for collecting information for 10 different projects. I need to write VBA code to attach to a button so that I can first filter information on all 10 worksheets then printout this filtered information then take the filter off so all rows of information are available. There are 2 pieces of information that I need to filter in rows P5:P625: "ProjName" and "Development". For Filtering, I thought the following code could work:
    Code:
    Sheets("P1").Range("P5:P625").Autofilter Field:=1,Criteria1:="Development", criteria2:="ProjName"
    , continuing this for each worksheet but then I get stuck as to how to print out the information and then "unfilter it". Ideally, I would like this information to print out without pagebreaks. In essence, 1 report. I haven't started this because I'd like a clear way to start. Any suggestions? Thanks!

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Need to adjust your filter, you are only filtering on field 1. Here is some code from an earlier post of mine

    Code:
    'add the filters
    With Worksheets("Data")    
        .AutoFilterMode = False    
        .Range("A1:S1").AutoFilter    
        For j = 2 To k        
            If oTrends(i, j) <> "" Then .Range("A1:S1").AutoFilter Field:=j, Criteria1:=oTrends(i, j)    
        Next
    Then
    Code:
    ActiveSheet.Printout   'or ActiveSheet.PrintPreview if you want to preview options before printing
    Should only print the visible data. If you need to set the range to limit to the filtering area try
    Code:
    ActiveSheet.AutoFilter.Range.PrintPreview
    lastly add .AutoFilterMode = False to remove the filtering

  3. #3
    Thank you. I will try this later. Question, I am not familiar with the following code:
    For j = 2 To k
    If oTrends(i, j) <> "" Then .Range("A1:S1").AutoFilter Field:=j, Criteria1:=oTrends(i, j)
    what does this do? Now, since I want to filter and print out from 10 worksheets with 1 push of a button, should I do the above as-is 10 times (filter sheet P1, print out, unfilter, then sheet P2). There is no way to get this to print out as 1 continuous list, is there? Thanks again. I will let you know how this work out as soon as I try.

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Quote Originally Posted by katkth7533 View Post
    Thank you. I will try this later. Question, I am not familiar with the following code: what does this do? Now, since I want to filter and print out from 10 worksheets with 1 push of a button, should I do the above as-is 10 times (filter sheet P1, print out, unfilter, then sheet P2). There is no way to get this to print out as 1 continuous list, is there? Thanks again. I will let you know how this work out as soon as I try.
    No that was just sample code from a previous project.
    1: Using the

    With Worksheets("Data")You will need to change this to run thru each of your ten sheets. You can either put the sheet names in an array and with
    for each sht in SheetArray
    OR
    for each sht in activeworkbook.sheets (if it must run on all sheets)

    2: Then set the filters
    I'm confused are you filtering on two columns or one column, also your range, only filtering column P? What about the rest of the data
    If one column, You are correct
    .Range("A1:P1").AutoFilter Field:=1, Criteria1:="ProjName", Criteria2:="Development"
    If two columns then
    .Range("A1:P1").AutoFilter Field:=1, ProjName:="abc"
    .Range("A1:P1").AutoFilter Field:=2, ProjName:="xyz"

    above done on my tablet, may be minor typos

  5. #5
    Oh ok. thanks for that clarification. I am only sorting on 1 field in column P - I want to filter out "ProjName" and "Development", so I will use the first option. Thanks again. I haven't hjad a chance to try this. I was side tracked yesterday with something else so I am trying today. Have a great weekend!

Tags for this Thread

Posting Permissions

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