Retrieving a chart using VBA

[EBahn]

I have two charts, Chart 1 and Chart 2. I would like to be able to add one of the two charts to another sheet using a specific value or cell to determine which chart to use. Basically if value in cell (or if I can use a value with out a cell) is greater or less than say 8363. If greater, use Chart 1 if less, use chart 2. Is there anyone who can give a hand? Much appreciated.

 

[Domenic]

The following code assumes that Sheet1 contains 'Chart 1' and 'Chart 2'. It also assumes that cell B2 on Sheet2 specifies the value that determines which chart to copy and paste. Note that the chart is copied and pasted at cell D2. Also, note that the code needs to be placed in the code module for Sheet2 (right-click the sheet tab for Sheet2, select View Code, and copy/paste the code into the code module for the sheet).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim chartCopied As Boolean

    If Target.CountLarge > 1 Then Exit Sub
 
    If Target.Address <> "$B$2" Then Exit Sub

    Const CHART_NAME As String = "MyChart" 'change the name as desired
 
    On Error Resume Next
    Me.ChartObjects(CHART_NAME).Delete
    On Error GoTo 0
 
    If Len(Target.Value) > 0 Then
        chartCopied = False
        If Target.Value > 8363 Then
            ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Copy
            Me.Paste Me.Range("D2")
            chartCopied = True
        ElseIf Target.Value >= 0 Then
            ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 2").Copy
            Me.Paste Me.Range("D2")
            chartCopied = True
        End If
        If chartCopied = True Then
            With Me
                .ChartObjects(.ChartObjects.Count).Name = CHART_NAME
            End With
        End If
    End If
 
 
End Sub

Hope this helps!

 

[EBahn]

Thank you very much. I will try to use this and see if I can't use the assumptions in the way it would work. I could send you the workbook I am trying to deal with and see if it can work. These "charts" are aircraft balance envelopes that I am trying to incorporate into one workbook. Right now I have made two separate worksheets and am trying to put into one worksheet.