VBA Code to Output to .pdf Multiple Charts per Sheet in Multi-sheet Workbook

[MedicMutt]

Hello.

First, sorry about the long title.

I've been searching for a solution without much success. Having stumbled upon VBA by accident (I've only been a casual user with the knowledge that Excel is powerful beyond my comprehension), I have been trying to automate chart outputs for a personnel functions tracker.

Each employee has a sheet devoted to them that I enter data points into. Each sheet contains three charts. I perform monthly outputs to show my employees how they're tracking.

As of right now, I have a stop-gap measure that creates three separate .pdfs from each sheet when I run the macro. I have to run the macro once for each sheet.

Sub PrintEmbeddedCharts()
     Dim ChartList As Integer
     Dim X As Integer
     ' Variable chartlist stores a count of all embedded charts.
     ChartList = ActiveSheet.ChartObjects.Count
     ' Increments the counter variable 'X' in a loop.
     For X = 1 To ChartList
         ' Selects the chart object.
         ActiveSheet.ChartObjects(X).Select
         ' Makes chart active.
         ActiveSheet.ChartObjects(X).Activate
         ' Prints one copy of active chart.
         ActiveChart.PrintOut Copies:=1
     Next
End Sub

I found this VBA code that performs the function I would like to transition to; however, the chart printouts show no meaningful data.

*All Charts in Workbook to Separate pdf for Each Sheet*

Sub AllChartsInWorkbookToPDF()
'--makes a separte pdf file with one chart per sheet
'     for each sheet in ActiveWorkbook with any embedded charts

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.ChartObjects.Count > 0 Then _
            Call MakePDFBookFromWorksheet(ws)
    Next ws
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

Private Function MakePDFBookFromWorksheet(ws As Worksheet)
'---make a pdf file of all charts in worksheet ws
'      with one chart per sheet
    
    Dim cht As ChartObject
    Dim wsTemp As Worksheet
    Dim sChartRange As String
    
    '--create new temporary workbook
    ws.Copy
    '--wsTemp will have the embedded charts to be processed
    Set wsTemp = ActiveSheet
    
    For Each cht In wsTemp.ChartObjects
        '--copy each chartObject a new sheet
        Sheets.Add After:=Sheets(Sheets.Count)
        With cht.Chart.ChartArea
            .Copy
            ActiveSheet.Paste
            '--set print area to range of chart
            With ActiveSheet.ChartObjects(1)
                .Top = 0
                .Left = 0
                sChartRange = Range(.TopLeftCell, _
                    .BottomRightCell).Address
            End With
            
            Application.PrintCommunication = False
            With ActiveSheet.PageSetup
                .PrintArea = sChartRange
            End With
            Application.PrintCommunication = True
    
            Range("A1").Select 'deselect chart
        End With
    Next cht
    '--delete temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
    
    '--modify page setup for all sheets
    Call SetupPages
    
    '--export temp workbook as PDF
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ws.Parent.Path & "\" & ws.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

    '--close temp workbook
    ActiveWorkbook.Close SaveChanges:=False
End Function

Private Function SetupPages()
    '--modify page setup for all sheets
    Dim i As Long
    Dim sSheetnames() As String
    
    With ActiveWorkbook.Sheets
        ReDim sSheetnames(1 To .Count)
        For i = 1 To .Count
            sSheetnames(i) = .Item(i).Name
        Next i
    End With
    
    Sheets(sSheetnames).Select
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        '--modify these for your desired setup
        .LeftMargin = 36 ' 36 points = 0.5 inches
        .RightMargin = 36
        .TopMargin = 36
        .BottomMargin = 36
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    Application.PrintCommunication = True
End Function

It outputs all three charts from each sheet into one .pdf file that is named after the parent sheet the charts are from (i.e. Lastname, Firstname.pdf). The only issue is that the charts do not contain any of the source data that the charts source from. I am guessing it is an issue with the "Range" but can't be sure as I don't even know what I don't know...

Please forgive my lack of knowledge. "Pedestrian" may be too nice to refer to what I know about this program.

Thank you all for any help you can provide.

 

[John_w]

It outputs all three charts from each sheet into one .pdf file that is named after the parent sheet the charts are from (i.e. Lastname, Firstname.pdf). The only issue is that the charts do not contain any of the source data that the charts source from.

I think the problem is caused by this line which deletes the temporary sheet (in the temporary workbook) containing the source data for all the charts, before the PDF is created:

    wsTemp.Delete

wsTemp is the first sheet in the temporary workbook and the charts are on the sheets with indexes 2, 3 and 4. The solution is to not delete the temporary sheet and export only sheets 2, 3 and 4 (by grouping them) as the PDF, instead of the whole workbook.

Replace:

    '--delete temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
    
    '--modify page setup for all sheets
    Call SetupPages
    
    '--export temp workbook as PDF
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ws.Parent.Path & "\" & ws.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

with:

    '--modify page setup for all sheets
    Call SetupPages
    
    '--group sheets with charts for PDF output
    Dim i As Long
    With ActiveWorkbook
        .Worksheets(2).Select
        For i = 3 To .Worksheets.Count
            .Worksheets(i).Select Replace:=False
        Next
    End With
    
    '--export grouped sheets as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ws.Parent.Path & "\" & ws.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[MedicMutt]

Perfection! I can't thank you enough!

I have much to learn.