Printing Sections of Worksheet Using AutoFilter and ListBox Values

[RyanH]

I have a global schedule worksheet that has a list of sales peoples intials
in Col.C. When the user wants to print the Sales Persons schedule a userform
is called with a ListBox that contains all the sales people on the global
schedule. The ListBox has these properties: BoundColumn=1 (which contains
the Sales Persons Initials) ColumnCount=2, TextColumn=1. The user then
selects the people she wants to print, then click print. The code below uses
AutoFilter to filter the sales people she selected from the Global Schedule.

Problem is no matter what sales person I select from the listbox it prints
all the sales peoples schedules, why? I need Criteria1 to equal the selected
values in Column 1 of the Listbox.

Private Sub btnPrint_Click()

Dim rngFilterRange As Range

Set rngFilterRange = Sheets("Global Schedule").UsedRange

With lboSalesPeople
For i = 0 To .ListCount - 1
If .Selected(i) Then
rngFilterRange.AutoFilter Field:=3, Criteria1:=.Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Next i
End With

End Sub

 

[RyanH]

I think I figured it out, but have another question. I used

rngFilterRange.AutoFilter Field:=3, Criteria1:=.List(i)

It prints the schedule fine requested just fine, but it also prints alot of
blank schedules. I think it is trying to print all the schedules that are
hidden, why? If not, any ideas why it is printing other sheets other that
what was requested?

 

[JLGWhiz]

It only prints what you tell it to print. So whatever you have as
SelectedSheets is what it will print, even if they are blank. If the sheet
you only want to print one sheet then use the sheet name. You can set the
print preview so that you can check what is selected for printing, before it
prints and remove any unwanted sheets at that time. Go to
File>PageSetUp>Sheet>Options and check the Print Preview box. It will always
let you preview what is about to be printed. You can always remove it if it
annoys you.