VBA Code to Print Multiple Worksheets with Filtering

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
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!
 
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 [B]Field:=j[/B], Criteria1:=oTrends(i, j)    
    Next

Then
Code:
[COLOR=#333333]ActiveSheet.Printout   'or [/COLOR][COLOR=#333333]ActiveSheet[/COLOR][COLOR=#333333].[/COLOR]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:
[COLOR=#333333]ActiveSheet.AutoFilter.Range.PrintPreview[/COLOR]

lastly add .AutoFilterMode = False to remove the filtering
 
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.
 
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
 
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!
 
Back
Top