Macro that copy-paste a chart depending on cell content

user_10291791 0
Viewed 97

Beginner macro-ist here with a question! I am trying to have a drop-down menu control what chart is displayed on a worksheet.

Right now I have a drop-down menu with categories (say A, B and C).

Somewhere on my sheet I have created a chart for each of these category (say a chart named payA, payB and payC).

Next, I have a cell that refer that concatenate both the word "pay" and the letter of the drop-down menu to make up the equivalent of the chart name.

I want my macro to delete the chart on the dashboard (named payDisplay), copy the appropriate chart depending on the cell (the concatenate would be in the cell EY38 in this example), paste it in the appropriate cell (B34 in this example, where payDisplay was originally) and then rename the new chart payDisplay.

Sub Change_Pay()
    Dim c As String

    c = Range("EY38").Value

    ActiveSheet.ChartObjects("payDisplay").Delete
    ActiveSheet.ChartObjects(c).Copy
    ActiveSheet.Range("B34").Select
    ActiveSheet.Paste
    ActiveChart.Parent.Name = "payDisplay"
End Sub

My macro keeps crashing on the ActiveSheet.ChartObjects(c).Copy line.

Whenever I open the worksheet, I get the bug and proceed to paste a chart in the right place and name is payDisplay and the macro is working just fine at that point. After saving and reopening the file, the macro doesn't work anymore (maybe that info is useful).

vba excel
1 Answers

Ok, I think I can help you:

please check how the cell EY38 is formatted. Remove any " or ' those will cause an error.

Secondly an advice from a guy who also started recording macros:

Do whenever possible not use activate and select. This is slow, painfull to analyse and prone to errors.

Here an example of a code that does the same thing as your but without activate and select.

Sub Change_Pay()
    Dim c As String
    Dim COdel As ChartObject
    Dim COcopy As ChartObject
    Dim sht As Worksheet
    c = Range("EY34").Value
    Set sht = Worksheets(1) ' worksheet object instead of activesheet

    Set COdel = sht.ChartObjects("payDisplay") ' Chartobject instead of activesheet.chartobjects("")
    Set COcopy = sht.ChartObjects(c)

    COdel.Delete
    COcopy.Copy Destination:=sht.Range("B34") ' No need to select and paste if you give it the destination
    COcopy.Parent.Name = "payDisplay"
End Sub
user_80193790
Related