I am trying to create a generic tool to analyse any Excel file so that the user can select what worksheets need to be copied to a new excel file. The tool is designed to remove all macro and formulas. By Copy and PasteSpecial Values and Formats I am close to achieving my goal. The issue I have is embedded graphs in worksheets are not copied.
How do I copy Embedded Grpahs with VB.
Last edited by PhatPhil; 11-29-2010 at 05:03 AM.
First copy the range including those cells covered by the chart.
Paste to the new sheet.
The re-copy and pastespecial the values.
The chartobject will still reference the original cells so you either need to update the chart data references or replace them with static array values.
If you create a copy of the worksheet the chart references will update automatically. You could then copy/pastespecial the cell contents
Andy thanks for responding.
There is where I am getting confused, I am copy the whole Worksheet which I would presume would include the range associated with the graph. As I am designing a generic tool the graph could be any where on the worksheet.
are you copying the sheet or all the cells?
It maybe helpful if you posted a workbook example and code.
You can also check the setting for "copy, cut, sort objects with cells." via Tools > Options > Edit.
Cut, copy, and sort objects with cells Keeps graphic objects, buttons, text boxes, drawn objects, and pictures with their associated cells whenever you cut, copy, filter, or sort on a worksheet.
Re: VB Copy embedded Graphs
I missed the trick with your post. I was using Paste Special To Paste the Values and then the Formats which gave me the worksheet but excluding the graph. I am now using 'Activesheet Paste' which is now transfering all the data and then Pastespecial Values which is removing all the formulas.
The issue I have now is although the graph is present, the source data is linked to donating excel file not the new one I created which you mentioned would be a problem in your first post.
You mentioned copying the worksheet, I am using the code 'cells select' and 'selection copy' to copy the original is there some better VB code which does the copying.
This will create a copy worksheet at the end of the workbook.
activeworkbook.Worksheets("Sheet1").copy after:=activeworkbook.Sheets(activeworkbook.Sheets.Count)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
PhatPhil 
Register To Reply
Bookmarks