1
1drunkbrit
Hi everyone
For quite some time now, I've been using 5 near identical macros t
help me print off some bi-monthly reports that use an "overview" shee
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 abou
different staff members who all have id numbers. The overview pag
shows these ID numbers and in the following 5 columns, how many sale
have been made.
At the top of each column i have a button that runs an individua
macro. I hit the dedicated macro (lets say for 2005) and i
automatically cycles through, finding the first ID number in th
"overview" which has a figure greater than zero. When it finds one, i
then selects the "2005" worksheet and displays all the entries for tha
collar number and prints it. It then returns to the "overview" to fin
the next collar number, and so on.
I used to have to do this all manually, but this has significantly cu
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 rewritte
so that instead of printing the results for each year on 5 separat
button presses, could it be reprogrammed so that it checks the overvie
page for no zeros, and then prints all that persons years off, befor
cycling to the next person?
eg, I want it so that it finds staff number 10364 (from column B) o
the control sheet then looks in the 2000 column (column C) on th
control sheet, and if greater than zero, goes to the worksheet for 200
and prints any entries with that ID number (column F on workshee
"2000"). I then want it to return to the control sheet and chec
columns D-G (2001 - 2005) in turn doing the same, before moving to th
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 appreciate
For quite some time now, I've been using 5 near identical macros t
help me print off some bi-monthly reports that use an "overview" shee
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 abou
different staff members who all have id numbers. The overview pag
shows these ID numbers and in the following 5 columns, how many sale
have been made.
At the top of each column i have a button that runs an individua
macro. I hit the dedicated macro (lets say for 2005) and i
automatically cycles through, finding the first ID number in th
"overview" which has a figure greater than zero. When it finds one, i
then selects the "2005" worksheet and displays all the entries for tha
collar number and prints it. It then returns to the "overview" to fin
the next collar number, and so on.
I used to have to do this all manually, but this has significantly cu
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 rewritte
so that instead of printing the results for each year on 5 separat
button presses, could it be reprogrammed so that it checks the overvie
page for no zeros, and then prints all that persons years off, befor
cycling to the next person?
eg, I want it so that it finds staff number 10364 (from column B) o
the control sheet then looks in the 2000 column (column C) on th
control sheet, and if greater than zero, goes to the worksheet for 200
and prints any entries with that ID number (column F on workshee
"2000"). I then want it to return to the control sheet and chec
columns D-G (2001 - 2005) in turn doing the same, before moving to th
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 appreciate