Print Filters using Macros

[aarbogast87]

Hello Everyone,

I'm new to macros but want to use it to make my work easier!
I'm working with Excel 2013 and I build reports for work. Right now I have a spreadsheet with 3 columns and 660 rows. The spreadsheet is caseloads so column A has Caseworkers, Column B is clients, and Column C is task due.

I'm trying to use macro to automatically print each caseworkers caseload out. I have 57 different caseworkers that comprise Column A
I used the record Macro function to get this code but I don't want to copy and paste this code 57 times and put in each caseworkers name. Any ideas?
Thanks

Please Login or Register  to view this content.

[aarbogast87]

Essentially, I would like excel to auto filter Column A and print out each caseworkers caseload

[aarbogast87]

So I used this code:
Sub Macro2()

Dim noDupes As New Collection
Dim rw As Long
Dim itm As Variant
Selection.AutoFilter Field:=1
rw = ActiveSheet.AutoFilter.Range.Row
For Each cell In ActiveSheet.AutoFilter.Range.Columns(1).Cells
If cell.Row <> rw Then
On Error Resume Next
noDupes.Add cell.Value, cell.Text
On Error GoTo 0
End If
Next
For Each itm In noDupes
Selection.AutoFilter Field:=1, Criteria1:=itm
ActiveSheet.AutoFilter.Range.PrintOut
Next

End Sub


It worked but started printing tons of blanks after it.

[dflak]

Here is how I would do it. First use a pivot table to get a unique_listing of each person's name. Putting the person's name in the row header and removing the Grand Total should do this. It has the side advantage of putting them in alphabetical order.

Then overlay the pivot table results with a named dynamic range. Sounds scary, but it isn't. Here are the full details on how to do it: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

I will assume that the pivot table results are on sheet 1 in column A and the first name is in cell A4

Here's how do to it in your case: Go to the page with the pivot results. Go to Formulas -> Name Manager. Click New. Type in "Worker_Names" (without the quotes) in the name box. In the refers to box type =Offset( and then click on the first value in the pivot table. Intellisense will fill in the sheet name and the cell selected.

So it will look like =OFFSET(Sheet1!$A$4 so far. Then Type ,0,0,Counta( and select all of column A)

Now it looks like =OFFSET(Sheet1!$A$4,0,0,Counta(Sheet1!$A:$A.

Close the parenthesis and type -1,1)

So the command now looks like =OFFSET(Sheet1!$A$4,0,0,Counta(Sheet1!$A:$A)-1,1)

What this says is go to cell A4, go down zero rows and right zero columns and return a range COUNTA(A:A)-1 rows deep and 1 column wide. The reason for the minus 1 is we don't want to count the headers.

Then the following code should work

Please Login or Register  to view this content.

[aarbogast87]

My code is working but after it prints each caseworkers caseload it tries to print a thousand blank pages. Not sure what part of the code is causing that.

[dflak]

Makes me wonder if there is something wrong with the Print Range. It may not be a row issue, but it might also be a column issue. Try clearing the print range to see what you get

[aarbogast87]

It's still doing it. Would it have to do with Blanks being a filtered item? I'm not sure how to remove them if it is.

[dflak]

This is as far as I can take it without actually having the file.