"AutoPrint" type macro requires tweaking
 

Hi everyone

For quite some time now, I've been using 5 near identical macros to
help me print off some bi-monthly reports that use an "overview" sheet
that pulls data from 5 other worksheets, all within the one workbook.

the 5 sheets are the last 5 years of data, and contain info about
different staff members who all have id numbers. The overview page
shows these ID numbers and in the following 5 columns, how many sales
have been made.

At the top of each column i have a button that runs an individual
macro. I hit the dedicated macro (lets say for 2005) and it
automatically cycles through, finding the first ID number in the
"overview" which has a figure greater than zero. When it finds one, it
then selects the "2005" worksheet and displays all the entries for that
collar number and prints it. It then returns to the "overview" to find
the next collar number, and so on.

I used to have to do this all manually, but this has significantly cut
down the amount of time I used to spend printing off the forms.

I was wondering if there was anyway that the macro could be rewritten
so that instead of printing the results for each year on 5 separate
button presses, could it be reprogrammed so that it checks the overview
page for no zeros, and then prints all that persons years off, before
cycling to the next person?

eg, I want it so that it finds staff number 10364 (from column B) on
the control sheet then looks in the 2000 column (column C) on the
control sheet, and if greater than zero, goes to the worksheet for 2000
and prints any entries with that ID number (column F on worksheet
"2000"). I then want it to return to the control sheet and check
columns D-G (2001 - 2005) in turn doing the same, before moving to the
next ID number in the control sheet and starting the process again.

Please find below a copy of the 2005 macro that I use

Sub PrintFilter2005()
'
' Filter by ID Ref and print where records are open
' Procedure name: PrintFilter2005()
' Edit date: 04/01/05


' Define VB variables
Dim idCell As Object, idRef As Integer
' Select Database worksheet and unprotect
Worksheets("2005").Select
ActiveSheet.Unprotect
' Select Control Worksheet and cell A1
Worksheets("Collar Numbers").Select
Range("A1").Select
' Loop through ID range checking if offset has a value
For Each idCell In Range("CONTROL_ID")
If (idCell.Offset(0, 5).Value 0) Then
idRef = idCell.Value
' Filter Database and print where offset is value
Worksheets("2005").Select
Selection.AutoFilter Field:=6, Criteria1:=idRef
ActiveSheet.PrintOut Copies:=1, Collate:=True
End If
Next
' Return filter to show all
Selection.AutoFilter Field:=6
' ActiveSheet.Protect
Worksheets("Collar Numbers").Select
End Sub

If anyone can help, it would be much appreciated


--
1drunkbrit
------------------------------------------------------------------------
1drunkbrit's Profile: http://www.excelforum.com/member.php...o&userid=26598
View this thread: http://www.excelforum.com/showthread...hreadid=398713