Urgent - print macro button

[jin]

Hi,

I need to know how to make a box that says "print" which will print out pages in my excel file... What I am picturing is this:

Tab 1 Y
Tab 2 Y
Tab 3 N
... etc.

Making a table as the one above, and depending on whether I input "Y" or "N", it will print that tab...

i've seen this in some financial models and would like to incorporate it into my model. If you could offer some detailed help, it would be greatly appreciated. Please note that I am a complete beginner and would need to be taken step-by-step.

Thanks again.

 

[VoG]

Sorry but I don't understand...

Are those cells in I that you are checking on each worksheet or just one. If on one sheet, which cells in I determine which sheet to print - if I12 <> 0 which sheet should be printed and so on.

 

[gadgetmeister]

Sorry but I don't understand...

Are those cells in I that you are checking on each worksheet or just one. If on one sheet, which cells in I determine which sheet to print - if I12 <> 0 which sheet should be printed and so on.

In one workbook there are 6 sheets (tabs).
On sheet 1 are the results from sheets 2-6
The results are in column "I" at positions as previously identified.
Depending on what the results are when displayed on sheet 1, I am trying to NOT print sheets 2-6 if the results in their tables are "0", as if the answer is nothing then I don't need to print the page.

So...... on Sheet 1 where the results are -
at positions "I16" & "I18" are results from two tables on sheet 4
at positions "I20" & "I22" & "I24" are results from 3 tables on sheet 5
at positions "I26" & "I28" & "I30" are results from 3 tables on sheet 6

Therefore -
if the results at "I16" or "I18" on sheet 1 are not "0", then print sheet 4
if the results at "I20" or "I22" or "I24" on sheet 1 are not "0", then print sheet 5
if the results at "I26" or "I28" or "I30" on sheet 1 are not "0", then print sheet 6

Have I made this clearer or worse?!

 

[Excelestial]

Whew! Next time...just hit the button to record a macro and assign the macro to an inserted form control. Takes approximately 1 minute. <<scratching head>>

 

[gadgetmeister]

Whew! Next time...just hit the button to record a macro and assign the macro to an inserted form control. Takes approximately 1 minute. <<SCRATCHING head>>

Yeah, tried that already but it doesn't work if you do that 'cos the pages to print depends on the results in the tables and they vary every time you use the file because it's making calculations based on inputs you give.

If you record the macro that way you always get the same pages printed.

 

[VoG]

Try this:

Sub SelectSheets()
With Sheets("Sheet1")
    If Application.Sum(.Range("I16, I18")) > 0 Then Sheets("Sheet4").PrintOut
    If Application.Sum(.Range("I20, I22, I24")) > 0 Then Sheets("Sheet5").PrintOut
    If Application.Sum(.Range("I26, I28, I30")) > 0 Then Sheets("Sheet6").PrintOut
End With
End Sub

 

[gadgetmeister]

Sorry for not explaining very clearly what it was I was trying to achieve!

I'll try this again in the morning with your suggestion when I get back to the office.

ray:

Thanks for all your help VoG, I'll report back to you as soon as I can.

 

[VoG]

Office on Saturday

Hint: to try this out without melting your printer and destroying a rain forest replace .PrintOut with .PrintPreview for testing purposes

 

[gadgetmeister]

Office on Saturday

Hint: to try this out without melting your printer and destroying a rain forest replace .PrintOut with .PrintPreview for testing purposes

PETER, YOU ARE AN ABSOLUTE STAR!!!!!

Final code looks like this -

Sub SelectSheets()
    '   Many thanks to Peter aka VoG MrExcel MVP who created this script!
With Sheets("PROJECT COSTING")
    If Application.Sum(.Range("I41")) > 0 Then Sheets("PROJECT COSTING").PrintOut
    If Application.Sum(.Range("I12")) > 0 Then Sheets("NURSECALL").PrintOut
    If Application.Sum(.Range("I14")) > 0 Then Sheets("FIRE").PrintOut
    If Application.Sum(.Range("I16, I18")) > 0 Then Sheets("INTERCOMS + OTHER STUFF").PrintOut
    If Application.Sum(.Range("I20, I22, I24")) > 0 Then Sheets("TOSHIBA + DECT").PrintOut
    If Application.Sum(.Range("I26, I28, I30")) > 0 Then Sheets("ACCESS CONTROL").PrintOut
End With
End Sub

Many thanks for all your patience and help!

 

[johnjch]

OK,

I tweaked the code a bit to return to the starting sheet:

Sub SelectSheets()
' John Walkenbach
' www.j-walk.com
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet, FinalSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

Set FinalSheet = ActiveSheet

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
FinalSheet.Activate
End Sub

My bad for not catching that, I haven't used the code in years.

I also found this to be extremely helpful in a workbook I'm building but I have one twist. I would like to number each page printed i.e 1 of 10, 2 of 10 etc.. If I set up a footer on each page they all print as 1 of 1. Thanks John

 

[Trewsue]

Hi, The macro posted here will prove to be invaluable to me, just wondering whether the "Print" can point to the "Print Options" dialogue box instead.
Many of the computers on our network have cutepdf as the default printer so staff actually have to choose the appropriate computer for the document they are printing.

Many thanks
Sue