troubleshooting Question

excel vba copying chart objects to a new workbook

Avatar of LuckyLucks
LuckyLucks asked on
Microsoft Excel
6 Comments1 Solution1179 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
LuckyLucks

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros