vba to make all charts in ws reference active sheet

[jeffreyweir]

Hi there. I'm very new to VBA, and I need a macro that makes all embedded charts on a worksheet reference that worksheet. Situation arises because I copied a worksheet with heaps of charts on it, but the charts in the new copy of the worksheet all reference the old worksheet.

Can someone help me out with this.

Many Thanks

Jeff

 

[jeffreyweir]

I found a cool non-vba workaround to this. Normally, when you create a copy of a worksheet, any embedded graphs continue to reference the old worksheet.

BUT... if you right click on the tab you want to copy, then select 'To new book' under the "To book" list, then hit OK the graphs in the new worksheet copy reference that new sheet, rather than the old.

So all you have to do then is rightclick on the tab on the new worksheet copy, and copy it back to the original sheet.

Neato!

 

[Andrew Poulsom]

For me in Excel 2000 copying a sheet within the workbook also results in the charts' references to change to the copied sheet. So I don't think your step is necessary.

The references remain unchanged only if you copy the cells or the charts to another worksheet.

 

[jeffreyweir]

Hi Andrew. After reading your comment, I checked again, and it seems my referencing issue is just happening on one workbook, for some reason. If I open a new book, and create a few graphs, then any embedded graphs in the copy reference the new sheet copy, just as happens for you.

But on my original worksheet, not so. Granted, there are about a zillion graphs in the worksheet concerned, so this might be causing excel to do something different for this particular worksheet.

I found a few posts on the net with other people having a similar problem. Perhaps it only rears its head when there's heaps of graphs involved, or some other factor.

By the way, in your reply, hid you mean to say that te references remain unchanged only if you copy the cells or the charts to another workbook (rather than worksheet)? If not, this doesn't seem to sqare with your comment that copying a sheet within the workbook also results in the charts' references to change to the copied sheet. Or (just as likely) Ive misinterpreted your statement.

Thanks for your comment.

Jeff

 

[mbrant]

Hi,

I'm having a similar problem so I hope nobody minds if I piggyback it here.

My source worksheet contains embedded charts with Dynamic Named Ranges. When I copy the sheet, the new sheet correctly updates the reference to 'new sheet' but it removes the names and substitutes the actual cell references, even though the dynamic names copy properly.

Any idea how I might circumvent this?

My hope is to do this in a macro to make it easier for others to use the Workbook when I'm done.

Thanks,
-Mike

 

[jeffreyweir]

Have you tried copying to a new workbook and back, just to see what happens? Who knows, this may just work for your scenario as it worked for me.