Link to home
Start Free TrialLog in
Avatar of Craig Small
Craig SmallFlag for Australia

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?
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

if you can post some code that is giving you the problem we can then look closer at what could be done better than just opening a sterile debate (that's my opinion at least)
gowflow
ASKER CERTIFIED SOLUTION
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Craig Small

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.
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.