Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
save original data after macro is run again | New Users to Excel | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for multiple charts | Excel Worksheet Functions |