Print Macro Specifications Entire Workbook

[Esjesu]

Hey everyone,


I have a bit of a problem with a printing macro that I hope you guys can solve for me.

Basically the macro I copied from another website fulfills most of my wishes as it prints out all chartsheets from Excel. However, it stops working because there are hidden chartsheets. I wish to embed an option that dynamically skips hidden charts and continues on printing - but my efforts remain fruitless still.

A second parter for this code would be that I want it to specifically target a list of named sheets (regular worksheets) and print those out as well.

As a result the button that the macro is under should print out all visible chartsheets and some named sheets that I've provided the names for in a workbook.

Hope you lot can help out with the struggle!
Thanks a lot in advance.

Option Explicit
Sub PrintCharts()
Application.ScreenUpdating = False
Dim ch As Object
Dim sh As Worksheet
Dim icount As Integer
icount = 0
  
    'Print Charts
    For Each ch In ActiveWorkbook.Charts
        icount = icount + 1
        ch.PrintOut
    Next ch
    
    MsgBox "Printing " & icount & " charts from Workbook " _
        & ActiveWorkbook.Name & ".", vbInformation, "Print Charts"


Application.ScreenUpdating = True


End Sub

Kind regards,
Esjesu

 

[Trebor76]

Hi Esjesu,

Welcome to MrExcel!!

See if the following meets your two requirements:

Option Explicit
Sub PrintCharts()

    Application.ScreenUpdating = False
    
    Dim ch       As Object
    Dim sh       As Worksheet
    Dim icount   As Integer
    Dim varMyTab As Variant

    icount = 0
      
    'Print Charts if the chart sheet is visible
    For Each ch In ActiveWorkbook.Charts
        If ch.Visible = True Then
            icount = icount + 1
            ch.PrintOut
        End If
    Next ch
    
    If icount > 0 Then
        MsgBox "Printing " & icount & " charts from Workbook " & ActiveWorkbook.Name & ".", vbInformation, "Print Charts"
    End If
    
    For Each varMyTab In Array("Sheet1", "Sheet2", "Sheet3") '<- Name of sheet(s) to be printed. Change to suit.
        If Sheets(CStr(varMyTab)).Visible = True Then
            Sheets(CStr(varMyTab)).PrintOut
        End If
    Next varMyTab
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Esjesu]

Hi Robert,


Thanks for the welcome!!

I'll give the code a whirl right now to see if it works the way I want it too, but based on what I read in the code I am pretty sure this is what I was looking for.

Will keep you posted!

Kind regards,

Esjesu

 

[Esjesu]

Hi Robert,


It seems to work perfectly.
One small however though, would it be possible to make the counter that's built in count how many charts and how many sheets were sent to the printer in one dialogue popup?

Currently it first mentions how many charts have been printed - and I have to press okay before it prints all of the other "named" sheets.

I would like it to come at the end and state Charts + named sheets, if possible.

Any idea how to tackle that?

Kind regards,


Esjesu

 

[Trebor76]

Hi Esjesu,

See how this goes (note I won't be checking in again until tomorrow):

Option Explicit
Sub PrintCharts()

    Application.ScreenUpdating = False
    
    Dim ch       As Object
    Dim sh       As Worksheet
    Dim iChart   As Integer
    Dim iSheet   As Integer
    Dim varMyTab As Variant
    
    iChart = 0
    iSheet = 0
      
    'Print Charts if the chart sheet is visible
    For Each ch In ActiveWorkbook.Charts
        If ch.Visible = True Then
            iChart = iChart + 1
            ch.PrintOut
        End If
    Next ch
    
    For Each varMyTab In Array("Sheet1", "Sheet2", "Sheet3") '<- Name os sheet(s) to be printed. Change to suit.
        If Sheets(CStr(varMyTab)).Visible = True Then
            Sheets(CStr(varMyTab)).PrintOut
            iSheet = iSheet + 1
        End If
    Next varMyTab
    
    If iChart > 0 And iSheet > 0 Then
        MsgBox iChart & " charts and " & iSheet & " sheets have been printed from Workbook " & ActiveWorkbook.Name & ".", vbInformation, "Print Editor"
    ElseIf iChart > 0 Then
        MsgBox iChart & " charts have been printed from Workbook " & ActiveWorkbook.Name & ".", vbInformation, "Print Charts"
    ElseIf iSheet > 0 Then
        MsgBox iSheet & " sheets have been printed from Workbook " & ActiveWorkbook.Name & ".", vbInformation, "Print Sheets"
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert