CODEWITHSUNDEEP
vbaexcelcharts
christophebedard
christophebedard

Reputation: 157

Moving embedded chart to a new chart sheet and renaming sheet

I'm trying to loop through charts in a specific worksheet and moving them to a new chart sheet at the end of all the sheets. Also, I want to rename the chart sheet and change the tab colour.

Here is my code:

Dim ws As Worksheet, co As ChartObject, c As Chart

Set ws = ThisWorkbook.Sheets("nameofSheet")
ws.Activate

For Each co In ws.ChartObjects
    co.Activate
    Set c = co.Chart

    'move chart to new sheet at the end
    c.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    'rename chart sheet
    c.Name = "newChartSheetName"

    'change colour
    c.Tab.Color = RGB(1,1,1)
Next co

However, it doesn't work and throws the error: Run-time error '1004': Method 'Move' of object '_Chart' failed

I've searched extensively but couldn't find any answer to this problem. I've tried using co (ChartObject) directly, but that didn't work. I've read that you should .Activate an object before doing something to it, but it didn't work either.

Upvotes: 0

Views: 2877

Answers (1)

Rory
Rory

Reputation: 34065

For an embedded chart, you need to convert to a chart sheet first:

Set c = c.Location(where:=xlLocationAsNewSheet, "newChartSheetName")
c.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

Upvotes: 2

Related Questions