We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
excel vba copying chart objects to a new workbook
Posted on 2011-03-09
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
For Each sSheet In NewBook.Sheets
With sSheet
For Each chtTemp In .ChartObjects
For Each objSeries In chtTemp.Chart.SeriesCollec
objSeries.Formula = Replace(objSeries.Formula,
Next
Next
End With
Next sSheet
NewBook.Save
NewBook.Close
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
For Each sSheet In NewBook.Sheets
With sSheet
For Each chtTemp In .ChartObjects
For Each objSeries In chtTemp.Chart.SeriesCollec
objSeries.Formula = Replace(objSeries.Formula,
Next
Next
End With
Next sSheet
NewBook.Save
NewBook.Close
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
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("NewWorkb
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
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("NewWorkb
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
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.
Do you know what the error means? I don't need a alternate solution that is totally operating on a different workflow.
>>>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
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
Try to copy your data sheets first and then chart object sheets. doing so, copied chart would not refer to previous workbook
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month11 days, 11 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.