Macro to Print out Different Dashboards from a List of Names?

[Goombah29]

Hello Excel People!

Here’s the low down: I have a dashboard that you can select a person’s name in a drop down list and then the information will change to be the data of that person; pretty standard vlokup, hlookup and sometimes a mix of both to fill in the form, change graphs, etc.


The Problem: Then I got asked to print each person, when faced with a list of 20 selecting each name and hitting print takes awhile. Even if I use a macro to print sheet, it doesn’t actually save any more time than just using ctrl+p.


What I’m after: Is there a macro that can be used to select a person or persons to print that person sheet w/out the dashboard page being selected to that name? Sort of like this except that instead of tabs on a workbook, names on a list: http://spreadsheetpage.com/index.php/tip/displaying_a_menu_of_worksheets_to_print/


Example: I want to print out Sue, Bob and Cary. I hit the macro button a menu pops up and ask who do I want to print? I select the 3 names and then it prints those 3 people w/ their proper information.


What to consider: it’s not just simply a name change, all the information changes, so if I have a macro to select names to print, will the rest of the data print correctly?


I don’t know if this is possible. I appreciate any help/feedback/suggestions!!


Thanks for reading!

 

[Hui]

Goombah


You can setup a list which contain the following

Name, Page, Print_Range, Portrait/Landscape, No_Pages, other settings (headers/footers etc), On/Off

and then setup a Macro to scan through the list

When the On/Off is On, print the Page and Range using all other settings


This allows you to have a number of pre-defined setups and turn them on/off as people ask for reports.

 

[Goombah29]

Thanks! I'll guess I'll have to sit down and try and figure that out. I have some VBA skill, but I have a long way to go to learn more.


Can I ask you another question on a semi-related but not really topic? I'm trying to do a if formula that has if the month is past than use this formula, if the month is not past use this other formula. I'm trying to get something to automatically switch btw Forecast and Actual, and for some reason it only works some of the time. I'm trying to use it against if it's greater than today() use the forecast, if less than today use actual. I have the date field above it as being the 1st of the month. Which I guess may be wrong as well, since it wouldn't be actual until the end of the month.


Thanks again for your help!

 

[Hui]

Goombah


Here is some code I used a long time ago to do what I mention above

[pre]

Sub Print_Summary_Reports()
'
' Print_Reports Macro
' Macro recorded 9/11/2004 by Hui:
'

Application.ScreenUpdating = False

Sheets("Print Areas").Select                                          'Selects Print Areas page

For Each c In Sheets("Print Areas").Range("B4..B23")                  'Selects print area column

If c.Value = "" Then End                                          'If it is a blank row Stop

If Sheets("Print Areas").Cells(c.Row, 4).Value = "On" Then        'Print the report

Orientation = Left(Sheets("Print Areas").Cells(c.Row, 7).Value, 1) 'Set Orientation variable
PWide = Sheets("Print Areas").Cells(c.Row, 8).Value           'Set Pages Wide variable
PTall = Sheets("Print Areas").Cells(c.Row, 9).Value          'Set Pages Tall variable
Copies = Sheets("Print Areas").Cells(c.Row, 10).Value          'Set No Copies variable
Footer = Sheets("Print Areas").Cells(c.Row, 11).Value         'Set Footer variable
Sheets(Sheets("Print Areas").Cells(c.Row, 5).Value).Select    'Select the relevent Sheet

ActiveSheet.PageSetup.PrintArea = Sheets("Print Areas").Cells(c.Row, 6).Value 'Select the relevent Print Area on the Sheet

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = Footer
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = PWide
.FitToPagesTall = PTall
.PrintErrors = xlPrintErrorsDisplayed
End With

If Orientation = "L" Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If

ActiveWindow.SelectedSheets.PrintOut Copies:=Copies, Collate:=True
End If
Next

Application.ScreenUpdating = True

End Sub

It requires a Sheet Print_Areas with the following layout starting in B3


No.	Description	Status	Sheet	Area	Land/Port	Pages Wide	Pages Tall	Copies	Footer
1	Summary Page	On	Summary	A1:AF89	L	1	1	1	Summary Page
2	Fuel Summary	On	Fuel Summary	A1:Z34	L	1	1	1	Fuel Summary
3	Report Page 1	Off	Summary	A1:Z150	P	1	1	1	Not Used
4	Report Page 2	Off	Summary	A1:Z150	P	1	1	1	Not Used
5	Budget Comparisson	Off	Summary	AH5:AO50	L	1	1	1	Budget Comparisson
6	Cover page	On	Cover_page	A1:L54	L	1	1	1	Cover Page

[/pre]
You will need to put a Button on the Print_Areas page which calls the Print_Summary_Reports Macro

Note that it only prints the Reports where the Status is "On"

 

[Goombah29]

Thanks! I'll play around w/ it!