Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA Printing all charts in workbook - blank pages

Status
Not open for further replies.

jlr123

Technical User
Feb 24, 2014
117
US
I am using the following code to print all charts in Excel 2010 workbook. However, it does not print all charts. Some are either partial chart or blank pages. any ideas? (I copied and pasted this VBA)I have tried another VBA code as well and it was the same.

Option Explicit
Sub PrintCharts()
'-------------------------------------------------------------------
'---Script: PrintCharts---------------------------------------------
'---Created by: Ryan Wells (wellsr.com)-----------------------------
'---Date: 04/2015---------------------------------------------------
'---Description: Orients and Prints all charts in an Excel Workbook-
'-------------------------------------------------------------------
Application.ScreenUpdating = False
Dim ch As Object
Dim sh As Worksheet
Dim icount As Integer
icount = 0
'Print Chart Objects
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
For Each ch In sh.ChartObjects
If ch.Height < ch.Width Then
ch.Chart.PageSetup.Orientation = xlLandscape
Else
ch.Chart.PageSetup.Orientation = xlPortrait
End If
icount = icount + 1
ch.Chart.PrintOut
Next ch
Next sh

'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


 
hi,
Code:
Sub PrintCharts()
 '-------------------------------------------------------------------
 '---Script: PrintCharts---------------------------------------------
 '---Created by: Ryan Wells (wellsr.com)-----------------------------
 '---Date: 04/2015---------------------------------------------------
 '---Description: Orients and Prints all charts in an Excel Workbook-
 '-------------------------------------------------------------------
    Application.ScreenUpdating = False
    Dim ch As Object
    Dim sh As Object
    Dim icount As Integer, x
    icount = 0
    'Print Chart Objects
    For Each sh In ActiveWorkbook.Sheets
        If sh.Type = xlWorksheet Then
            For Each ch In sh.ChartObjects
                If ch.Height < ch.Width Then
                ch.Chart.PageSetup.Orientation = xlLandscape
                Else
                ch.Chart.PageSetup.Orientation = xlPortrait
                End If
                icount = icount + 1
                ch.Chart.PrintOut
            Next ch
        Else
            If sh.ChartArea.Height < sh.ChartArea.Width Then
                sh.PageSetup.Orientation = xlLandscape
            Else
                sh.PageSetup.Orientation = xlPortrait
            End If
            icount = icount + 1
            sh.PrintOut
        End If
    Next sh
    
    MsgBox "Printing " & icount & " charts from Workbook " _
    & ActiveWorkbook.Name & ".", vbInformation, "Print Charts"
    
    Application.ScreenUpdating = True
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top