Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print Embedded Charts (EXCEL97 SR2)

    I have 6 charts on 1 WS and I would like to have a macro that will print them out each time I run the VBA code that refreshes them. I tried the following but it did not work:
    Sub PrintCharts()
    Dim i As Integer
    Worksheets("Avg Prod Rqmnt Graphs").Activate
    Application.DisplayAlerts = False
    For i = 1 To 5
    Worksheets("Avg Prod Rqmnt Graphs").ChartObjects(i).Chart.Select
    ActiveChart.PrintOut
    Next
    Application.DisplayAlerts = True
    End Sub

    So, I resorted to recording a macro and copying it over 6 times.
    Here is what the code that prints the first 2 charts looks like:
    Sub PrintProductionCharts()
    Sheets("Avg_Prod_Rqmnt_Graphs").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Windows("SSGrid_FORECAST.xls:1").SmallScroll Down:=14
    ActiveWindow.Visible = False
    Windows("SSGrid_FORECAST.xls:1").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    The recorded and adjusted macro works but I would like to know how to do this with fewer lines of code.
    Thanks,
    Stephen

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Print Embedded Charts (EXCEL97 SR2)

    Stephen,

    If all the charts are on the same sheet, the following should print them, one per page.<pre>Sub PrintCharts()
    Dim objCht As ChartObject
    For Each objCht In ActiveSheet.ChartObjects
    objCht.Activate
    ActiveChart.PrintOut
    Next
    End Sub</pre>


    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •