Copy Pasting Charts Excel VBA

Question

I am having a problem here with my VBA code.

This is the first code I am writing in VBA. Basically I want it to check a condition (Value of cell F7 in sheet2), and based on that I would like to paste one of two charts in sheet2.

    Private Sub CommandButton1_Click()


CommandButton1.Caption = "Stock Size Range"
CommandButton1.BackColor = 0
CommandButton1.ForeColor = 16777215

'Clear the chart area
 Charts("Chart41").ChartArea.Clear


If Sheets("sheet2").Range("F7") = 1 Then 'Aluminum Material


    Sheets("sheet3").ChartObjects("Chart666").Select
    Sheets("sheet3").ChartObjects("Chart666").Copy
    ChartObjects("Chart41").Paste

    Else

    Sheets("sheet4").ChartObjects("Chart888").Select
    Sheets("sheet4").ChartObjects("Chart888").Copy
    ChartObjects("Chart41").Paste

End If

End Sub

Thing is,, when I click on the command button, it gives me a Run-Time error "9": Subscript out of range and it points to the line ( Charts("Chart41").ChartArea.Clear)

Thank you,

Answer 1

(Sorry but I do not have enough reputation to write a comment and ask about more information, so I will do my best!)

Hello!

Maybe you need to go to the desired sheet where the chart will be pasted, create a chart and then associate an ID to it, for example "MyNewChart" (you change the chart ID in the "area" where is written "Node 5" in a red rectangle in this image https://i.sstatic.net/hf2Nq.png)

Then in your code, when you need to paste the chart, you can write:

Sheets("WriteSheetNameHere").ChartObjects("MyNewChart").Paste

HTH ;)