none
Create New Chart Sheet to Display Charts RRS feed

  • Question

  • I have a sheet with several charts displayed. The charts are sometimes too small to view the detail.  I want to add a macro button to the chart that either temporarily increases the size of the chart or create a chart sheet that the chart is copied to with the same effect.

    I recorded a macro that copies to a new chart sheet but it only works on the charts created from pivot tables.  Charts created with normal data ranges don't work and fails at Charts.add. This is the code that works with the chart created from a pivot table:

    Sub FTEAllChart9()
    '
    ' Macro1 Macro
    '
        ActiveSheet.ChartObjects("Chart 9").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.Copy
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.Paste
    End Sub

    Is there a solution to this in VBA?

    Cheers

    Jim

    Friday, October 12, 2018 12:35 AM

Answers

  • Thanks Lina,

    I didn't want to create a chart, just copy an existing chart to Chart Sheet so the detail was enlarged (the boss is as blind as a bat!). However, your code gave a notion of how I might get what I wanted. It might be a bit messing but it seems to work.

    I've modified the code to copy one of the small charts (Chart 1 of 4) in my ForecastChart sheet. It seems to work so I'll created one for each chart by name.  I can delete the sheets when I create the new period files.

    Sub ChartSheet_NewChart1()
    'create a new chart sheet and copy an existing named Chart


    'Add method of the Charts object adds a new empty chart sheet with a default name and places it immediately before the last sheet in the active workbook
    '
    Sheets("ForecastChart").Select
        Range("S2").Select
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.Copy
    ActiveWorkbook.Charts.Add Before:=Sheets(Sheets.Count)

    'the new chart sheet becomes the active chart
    With ActiveChart

        ActiveChart.Paste

    'set type of chart

    .ChartType = xlColumnClustered


    End With


    End Sub

    Thanks again for your help.

    Cheers

    Jim

    • Marked as answer by Jim from Oz Monday, October 15, 2018 12:44 AM
    Friday, October 12, 2018 8:31 AM

All replies

  • Hi Jim,

    The following code is to create a new chart sheet, please try the code as below:

    Sub ChartSheet_New()
    'create a new chart sheet
    
    
    'Add method of the Charts object adds a new empty chart sheet with a default name and places it immediately before the last sheet in the active workbook
    
    ActiveWorkbook.Charts.Add Before:=Sheets(Sheets.Count)
    
    'the new chart sheet becomes the active chart
    
    With ActiveChart
    
    'set type of chart
    
    .ChartType = xlColumnClustered
    
    'set the range of source data for the chart
    
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:D6")
    
    'rename the new chart sheet
    
    .Name = "NewChartSheet"
    
    'moves the chart sheet to a new location and places after the last sheet in the workbook
    
    .Move After:=Sheets(Sheets.Count)
    
    End With
    
    
    End Sub

    For more information, please review the following link:

    Use the Add method of the Charts object to add or create a new chart sheet.

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 12, 2018 2:59 AM
  • Thanks Lina,

    I didn't want to create a chart, just copy an existing chart to Chart Sheet so the detail was enlarged (the boss is as blind as a bat!). However, your code gave a notion of how I might get what I wanted. It might be a bit messing but it seems to work.

    I've modified the code to copy one of the small charts (Chart 1 of 4) in my ForecastChart sheet. It seems to work so I'll created one for each chart by name.  I can delete the sheets when I create the new period files.

    Sub ChartSheet_NewChart1()
    'create a new chart sheet and copy an existing named Chart


    'Add method of the Charts object adds a new empty chart sheet with a default name and places it immediately before the last sheet in the active workbook
    '
    Sheets("ForecastChart").Select
        Range("S2").Select
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.Copy
    ActiveWorkbook.Charts.Add Before:=Sheets(Sheets.Count)

    'the new chart sheet becomes the active chart
    With ActiveChart

        ActiveChart.Paste

    'set type of chart

    .ChartType = xlColumnClustered


    End With


    End Sub

    Thanks again for your help.

    Cheers

    Jim

    • Marked as answer by Jim from Oz Monday, October 15, 2018 12:44 AM
    Friday, October 12, 2018 8:31 AM
  • Hi Jim,

    You are welcome.

    Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Friday, October 12, 2018 8:52 AM
    Friday, October 12, 2018 8:51 AM