Loose Embedded Graph Values when copying sheet to new workbook

[Henk Stander]

Loose Embedded Graph Values when copying sheet to new workbook:

What I would like to do is to, with VBA, copy two sheets from a workbook to a new workbook without loosing any values or formatting. The one sheet contains formulas and embedded charts and the other is a chart sheet. Only values and formatting must be copied to the new workbook and the charts/graphs must retain there values and appearance. The problem I am experiencing is that the charts loose their values. See attached example sheet.

Thank you,

Regards

Similar thread at:
http://www.vbaexpress.com/forum/show...d-formats-only

[Winon]

Goeie Middag Henk Stander,

Instead of creating a new Workbook and wanting to Copy and Paste, let us try it this way round.

First of all, you will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the attached sample Workbook.

After clicking on the Button, a Save File As screen will appear where you may enter the new Workbook name.
NOTE: Be careful here, because if you don't enter a different name, the actual active Workbook you are working with will be replaced, with some Sheets lost, and stripped of all VBA Code!

See if you can make do with this, otherwise we shall have to consider other alternatives.

Baie dankie,

Regards

[Henk Stander]

Hi Winon,

Definitely an approach that can work.
This could be a problem: "First of all, you will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the attached sample Workbook."

This program will be run be others, rather excel illiterate. The intent is to at the end of the year run the macro, which will then save the file with a specific name relating to the year. This then becomes the data for the completed year.

I am still pursuing the original approach and have made some progress, not as elegant as your solution though.

In any case, this is very useful information and a very useful example, thanks.

Baie dankie

Regards.

[Andy Pope]

This seems to work

Please Login or Register  to view this content.

[Henk Stander]

This seems to work

Please Login or Register  to view this content.

It does indeed sir, I am honored to receive a reply from you. It does exactly what is required in a short to the point manner, thank you so much.

Regards