excel vba copying chart objects to a new workbook

hi

I have a source workbook , call it WorkbookA, with multiple sheets that have chartobjects. I am copying over all the sheets to a WorkbookB. However, on doing so, I find that the charts in the copied over chartobjects in WorkbookB refer to data set in WorkbookA.

How can I change this so that the charts in WorkbookB refer to data sets in WorkbookB?

I tried to change the Formula so that it refers to WorkBookB, but on doing so it gives a Run-Time error '1004' : Application-defined or object-defined error.

After copying over all the sheets from WorkbookA.xlsm to WorkbookB.xls, do the following

Set NewBook = Application.Workbooks.Open("WorkbookB.xls")
For Each sSheet In NewBook.Sheets
With sSheet
For Each chtTemp In .ChartObjects
For Each objSeries In chtTemp.Chart.SeriesCollection
objSeries.Formula = Replace(objSeries.Formula, "WorkbookA.xlsm", "WorkbookB.xls")
Next
Next
End With
Next sSheet

NewBook.Save
NewBook.Close

LuckyLucks

Asked: 2011-03-09

Advertise Here

Microsoft Excel

1 Solution

LVL 30

SiddharthRoutCommented: 2011-03-09

Here is a trick to it without writing too much code.

Rename the sheets which has a chart to say "Lucky1","Lucky2" etc...

Then in the code,

1) Add a new workbook.
2) Move (don't copy) the chart sheet and the source datasheet to the new workbook for example

Sheets("Lucky1").Move After:=Workbooks("NewWorkbook").Sheets(3)

3) Similarly move all sheets from the 1st one to the new book. Ensure that you handle errors. And then close the 1st workbook without saving so the saved version still contains the moved, i.e., removed, sheets

This will ensure that the new workbook's chart refer to the actual sheets.

Let me know if you get stuck.

Sid

LuckyLucksAuthor Commented: 2011-03-09

moving is not acceptable. I need to keep the original for a reason. also note that the original WorkBookA is a macro-enabled file while the second WorkbookB is not a macro-enabled file.

Do you know what the error means? I don't need a alternate solution that is totally operating on a different workflow.

LVL 30

SiddharthRoutCommented: 2011-03-09

>>>moving is not acceptable. I need to keep the original for a reason.

Your original file will remain untouched and that is why at the end of the code we will not save the original. If you upload your files, then maybe I can give you a sample code?

Sid

LVL 6

Faisal KhanCommented: 2011-03-09

Try to copy your data sheets first and then chart object sheets. doing so, copied chart would not refer to previous workbook

LuckyLucksAuthor Commented: 2011-03-09

found a similar question by brettdj that was great:

brettdj:

If you are copying the data range as well then you can just relink the destination workbook to itself (from the source workbook) using ChangeLink in VBA

This gets the Chart pointing to the data in the destination folder

Cheers

Dave

LuckyLucksAuthor Commented: 2011-03-14

original answer provided by brettdj

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

excel vba copying chart objects to a new workbook

Join & Write a Comment Already a member? Login.

Featured Post