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.

 

[Mldeuser]

Is there a certain spot in the macro that this should be inserted?

Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.PrintOut
Next ws
End Sub

Thanks
Mark

 

[Mldeuser]

Currently there are two buttons on the box that pops up one says ok the other says cancel can a third one be added to say print all

 

[gadgetmeister]

You could do something along these lines:

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("A1").Value <> 0 Then ws.PrintOut
  Next ws
End Sub

So would that mean if my cells were I12, I14, I16 etc that I would do something like this -

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I12").Value <> 0 Then ws.PrintOut
  Next ws
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I14").Value <> 0 Then ws.PrintOut
  Next ws
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I16").Value <> 0 Then ws.PrintOut
  Next ws
End Sub

Or have I completely missed the point you were making?!

 

[Mldeuser]

I was thinking of a "select all" option so I can either select the worksheets that I want to print or i could click "select all". Some of the workbooks have over 35 tabs so it would be great if I could just select all the first time I print then later just select the ones that were updated.

 

[Smitty]

So would that mean if my cells were I12, I14, I16 etc that I would do something like this -

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I12").Value <> 0 Then ws.PrintOut
  Next ws
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I14").Value <> 0 Then ws.PrintOut
  Next ws
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("I16").Value <> 0 Then ws.PrintOut
  Next ws
End Sub

Or have I completely missed the point you were making?!

You're close, but you're looping through the sheets 3x which isn't very efficient. I'd try to keep your conditions all within one loop.

 

[gadgetmeister]

Smitty, do you mean more like this then?

Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
If ws.Range("I12").Value <> 0 Then ws.PrintOut
If ws.Range("I14").Value <> 0 Then ws.PrintOut
If ws.Range("I16").Value <> 0 Then ws.PrintOut
If ws.Range("I18").Value <> 0 Then ws.PrintOut
If ws.Range("I20").Value <> 0 Then ws.PrintOut
If ws.Range("I22").Value <> 0 Then ws.PrintOut
If ws.Range("I24").Value <> 0 Then ws.PrintOut
If ws.Range("I26").Value <> 0 Then ws.PrintOut
If ws.Range("I28").Value <> 0 Then ws.PrintOut
If ws.Range("I30").Value <> 0 Then ws.PrintOut
Next ws
End Sub

 

[VoG]

Smitty, do you mean more like this then?

Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
If ws.Range("I12").Value <> 0 Then ws.PrintOut
If ws.Range("I14").Value <> 0 Then ws.PrintOut
If ws.Range("I16").Value <> 0 Then ws.PrintOut
If ws.Range("I18").Value <> 0 Then ws.PrintOut
If ws.Range("I20").Value <> 0 Then ws.PrintOut
If ws.Range("I22").Value <> 0 Then ws.PrintOut
If ws.Range("I24").Value <> 0 Then ws.PrintOut
If ws.Range("I26").Value <> 0 Then ws.PrintOut
If ws.Range("I28").Value <> 0 Then ws.PrintOut
If ws.Range("I30").Value <> 0 Then ws.PrintOut
Next ws
End Sub

Unless you want multiple copies perhaps

Dim ws As Worksheet, i As Integer
For Each ws In ActiveWorkbook.Worksheets
    For i = 12 To 30 Step 2
        If ws.Range("I" & i).Value <> 0 Then
            ws.PrintOut
            Exit For
        End If
    Next i
Next ws

 

[gadgetmeister]

I'll post that back into the macro in the morning and give it a go.
I'll let you know how I get on!
Thanks for your help guys!

 

[Mldeuser]

I was thinking of a "select all" option so I can either select the worksheets that I want to print or i could click "select all". Some of the workbooks have over 35 tabs so it would be great if I could just select all the first time I print then later just select the ones that were updated.


Is this possible ?
<!-- / message -->

 

[gadgetmeister]

VoG,

That seems to print all the pages even though some of the cell values on the front sheet are "0". The front sheet conatins the results, sheets 2 -6 contain the tables and calculations.
What I'm aiming for is the automated front sheet, plus any sheets where the results are not zero.

Could the problem be that I didn't mention the following -
I16 & I18 are results from the same sheet (page 4)
I20 & I22 & I24 are results from the same sheet (page 5)
I26 & I28 & I30 are results from the same sheet (page 6)

The code is what you suggested I try initially, cut & pasted below -
Sub SelectSheets()
Dim ws As Worksheet, i As Integer
For Each ws In ActiveWorkbook.Worksheets
For i = 12 To 30 Step 2
If ws.Range("I" & i).Value <> 0 Then
ws.PrintOut
Exit For
End If
Next i
Next ws
End Sub

I appreciate the help you guys are giving. This forum is the only way us newbies get in to working with Excel Macros. Algebra I can work with, but I'm no programmer!!

Cheers