Excel macro for printing multiple sheets...

[nicklas]

Hi!

Let's say I have a workbook containing of 10 sheets. I would like to create a macro that would allow me to do the following:

Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.

(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)

I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:

Sub Test_skriva_ut_flera_ark_samtidigt()
'
' Test_skriva_ut_flera_ark_samtidigt Macro
' 2008-03-13
'

'
    Sheets(Array("Sheet1", "Sheet3", "Sheet7")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("A1").Select
End Sub

I would really appriciate advice from you guys (and girls)

Thank you!

Kind regards,

Nicklas

[royUK]

Do an Advanced Search on my name with Print as the key word and you should find an example that should help

[davesexcel]

Try hiding the sheets you do not want printed, then print the entire workbook
example code

Sub Button1_Click()
    If Worksheets("Sheet1").Range("A1") = "" Then    'checks to see if A1 has data
        Sheets("Sheet1").Visible = False    'hides sheet if there is no data in A1
        If Worksheets("Sheet2").Range("A1") = "" Then
            Sheets("Sheet2").Visible = False
            If Worksheets("Sheet3").Range("A1") = "" Then
                Sheets("Sheet3").Visible = False
            End If
        End If
    End If
    Application.ActivePrinter = "PDF reDirect v2 on Ne01:"    'use macro recorder, to  print entire workbook, will not print the hidden worksheets
    ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:= _
                            "PDF reDirect v2 on Ne01:", Collate:=True

    Sheets("Sheet1").Visible = True    'unhides sheet
    Sheets("Sheet2").Visible = True
    Sheets("Sheet3").Visible = True


End Sub

[royUK]

Try this, note chane PrintPreview to Printout when you have finished testing.

Sub printShts()
    Dim ws     As Worksheet

    For Each ws In Worksheets
        Select Case ws.Index
            Case 1
                ws.PrintPreview
            Case 3, 7
                If ws.Cells(9, 1).CurrentRegion.Cells.Count > 1 Then ws.PrintPreview
            Case Else
        End Select
    Next ws
End Sub

Or

Sub printShts()
    Dim ws     As Worksheet

    For Each ws In Worksheets
        Select Case ws.Name
            Case "Sheet1"
                ws.PrintPreview
            Case "Sheet3", "Sheet7"
                If ws.Cells(9, 1).CurrentRegion.Cells.Count > 1 Then ws.PrintPreview
            Case Else
        End Select
    Next ws
End Sub

[ravishankar]

Hi
Try the following macro

Sub Macro1()
For a = 1 To 3
b = Choose(a, 1, 3, 7)
Worksheets(b).Activate
If b = 1 Or Worksheets(a).Cells(9, 1) <> "" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
MsgBox "printed " & Worksheets(b).Name
End If
Next a
End Sub

Prints sheet1 and optionally 3 & 7 if A9 is not blank.
Ravi

[nicklas]

Thank you royUK!!!! This worked excellent!

Try this, note chane PrintPreview to Printout when you have finished testing.

Sub printShts()
    Dim ws     As Worksheet

    For Each ws In Worksheets
        Select Case ws.Name
            Case "Sheet1"
                ws.PrintPreview
            Case "Sheet3", "Sheet7"
                If ws.Cells(9, 1).CurrentRegion.Cells.Count > 1 Then ws.PrintPreview
            Case Else
        End Select
    Next ws
End Sub

[royUK]

Thanks for letting me know. Glad it helped.

[nicklas]

Just one last question though, what does this code in the script mean exactly?

If ws.Cells(9, 1).CurrentRegion.Cells.Count > 1 Then ws.PrintPreview

Because when I tried to apply the macro where the cells have links in them but still equals zero it didn't work. For example: =Sheet5!A8 in one cell but A8 in Sheet5 is empty. The script worked when the cells being checked where completly empty but not when they contain references (although the reference cell is zero).

Best regards,

Nicklas

[VBA Noob]

The line means if the Worksheets range A9 currentregion (see below) cells count is greater than 1 then worksheet print preview

Currentregion
(The current region is a range bounded by any combination of blank rows and blank columns)

HTH

VBA Noob

[royUK]

Empty cells are empty. Cells with links containing formulas are not empty, therefore they are part of the CurrentRegion. You would need to check for zero values, maybe

Option Explicit

Sub printShts()
    Dim ws     As Worksheet

    For Each ws In Worksheets
        Select Case ws.Name
            Case "Sheet1"
                ws.PrintPreview
            Case "Sheet3", "Sheet7"
                If ws.Cells(9, 1).CurrentRegion.Cells.Count > 1 And .Cells(8, _
                    1).Value <> 0 Then ws.PrintPreview
            Case Else
        End Select
    Next ws
End Sub

[gordon_samways]

Thanks nice tip