Printing using Macros for Multiply Co-workers in Excel

[Gregory Sharofsky]

I work for a Cable Company and i have a worksheet that gives me details of
subscriber #'s. Each tab is a city. For example tab 1 is called Edina, MO;
tab 2 is called Hannibal, MO, etc. Now each tab is roughly about 4 pages. Now
i have to print these out for certain co-workers but the catch is each
co-worker requires different details from my report.

Co-worker #1 needs all tabs with all 4 pages of details
Co-worker #2 needs all tabs but only requires 1 page
Co-worker #3 needs all tabs but only requires pages 3 and 4.

Is there anyway to set up a macros where if i press Cntrl + P a message
window box appears that gives me the option on who to print for? And if so
how do i do that and set up the print jobs. If needed i can email the report
to you guys to look at.

Thanks Greg

 

[Dave Peterson]

How about just recording 3 (or more) macros that print exactly what you want.

Then you can just run these macros when you need to print that portion.

I'd name the macros nicely, too:
GregsPrintReport
....

 

[Paul]

Gregory,

Dave's suggestion is probable best, as you learn by doing. Also I am
surprised you don't have someone knowledgeable in Excel in
your organisation.

As a little mental exercise I did this:

Sub PrintingSettings()
'Purpose: A procedure to print pages according to user.
Dim vUserName As Variant

vUserName = Application.InputBox("Your user name
please(Case-sensitive).", "Print Macro User", , , , , , 2)

'If user presses cancel then out.
If vUserName = False Then Exit Sub

If vUserName = "User1" Then
'Print everything on each sheet
Worksheets("Edina, MO").Select
ActiveSheet.PrintOut Copies:=1, Collate:=True
Worksheets("Hannibal, MO").Select
ActiveSheet.PrintOut Copies:=1, Collate:=True
ElseIf vUserName = "User2" Then
'Print page 1 on each sheet
Worksheets("Edina, MO").Select
ActiveSheet.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Worksheets("Hannibal, MO").Select
ActiveSheet.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
ElseIf vUserName = "User3" Then
'Print pages 3 & 4 on each sheet
Worksheets("Edina, MO").Select
ActiveSheet.PrintOut From:=3, To:=4, Copies:=1, Collate:=True
Worksheets("Hannibal, MO").Select
ActiveSheet.PrintOut From:=3, To:=4, Copies:=1, Collate:=True
End If

End Sub

Copy this into a vb module in the workbook. Change User1, User2, User3
according to agreed user names.
Make sure the Worksheet names are exactly as your tab names.

It presumes that all print formatting is done.

If you want shortcut key then assign Ctrl+Shft+P, Ctrl+P is the generic one
Excel uses for general printing.

Regards
Paul