Craig Small

asked on

# Excel 2007 charts with delinked data losing data and numeric formatting

I need to consolidate worksheets from a number of different Excel workbooks. I'm using VBA to do the copying, which works well (by and large).

A number of the sheets contain charts. I was struggling with "delinking" that data from the charts, but I thought I'd found the solution to that problem. The only problem is that, when the data series become "embedded" in the chart, all formatting is lost, and I end up with my formatted dates as date serial numbers (less than helpful), and numbers that were previously quite happy as percentages now showing on a scale from 0 to 1.

Is this a problem which can be solved using VBA, or any other method for that matter?

A number of the sheets contain charts. I was struggling with "delinking" that data from the charts, but I thought I'd found the solution to that problem. The only problem is that, when the data series become "embedded" in the chart, all formatting is lost, and I end up with my formatted dates as date serial numbers (less than helpful), and numbers that were previously quite happy as percentages now showing on a scale from 0 to 1.

Is this a problem which can be solved using VBA, or any other method for that matter?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

andrewssd3, you've nailed my problem perfectly.

That's be right - it works in the versions either side of the one I'm using :-). I've been trying the "copy the data over" solution, but it seems to want to link back to the source sheet. I have a few more tricks up my sleeve to see if I can make that work, so I'll persevere.

Thanks very much for taking the time to reply - at least I know that I'm working with a know problem, and not just completely incompetent.

That's be right - it works in the versions either side of the one I'm using :-). I've been trying the "copy the data over" solution, but it seems to want to link back to the source sheet. I have a few more tricks up my sleeve to see if I can make that work, so I'll persevere.

Thanks very much for taking the time to reply - at least I know that I'm working with a know problem, and not just completely incompetent.

ASKER

I had a loop which copied all the sheets, one at a time, which didn't maintain the linkages. By grouping the sheets, and copying them as one block, the problem is solved - the references all come across cleanly, and refer to the sheets in the new workbook.

Again, thanks for your input.

Again, thanks for your input.

gowflow