Copying chart from one sheet to another and remove links

[drgwhizz]

Hi all,

Currently have the below code which selects one of three charts based on a value in a validation cell on a separate sheet and pastes the selected chart in cell m14 on the same sheet as the validation cell.

Please Login or Register  to view this content.

What I'd like to do is paste the chart but without the links to the original data. I presume pastespecial is the best approach? But I'm struggling to get anything to work.

I need the copied chart to remain as a chart; i.e. It can't be copied and pasted as a picture otherwise the ChartObjects(1).Delete won't work .

Thanks in advance.

DrG

[Andy Pope]

It depends on how complex your chart and it's data is.
See here for information.
http://peltiertech.com/Excel/ChartsH...ChartData.html

[drgwhizz]

It depends on how complex your chart and it's data is.
See here for information.
http://peltiertech.com/Excel/ChartsH...ChartData.html

Thanks for the link. From the information contained I've managed to remove the links from the selected chart using the code below.

Please Login or Register  to view this content.

Is there a way to select all charts in the active worksheet prior to this process, so that all the charts have links removed?

And is there also a way to combine the above with a process which formats the data labels into currency format with 2 decimal places?

EDIT: Solved the data label formatting issue with the code below:

Please Login or Register  to view this content.

So now just stuck trying to automate the process for all charts on the worksheet rather than just the active ones. I'm not sure what code is needed at the start of the process.

Thanks.

[Andy Pope]

Please Login or Register  to view this content.

[drgwhizz]

Thanks for all your help Andy,

I'd just managed to get some code working for me and was about to post. Although possibly not the most efficient code it appears to be working for me at the moment:

Please Login or Register  to view this content.

Managed to pull bits together from the link you provided earlier. +rep.

Problem SOLVED.