I have a spreadsheet project which produces various reports. I have a macro which copies the active report into a new book, simultaneously hard-coding any numbers from external sources whilst keeping formulae intact. The purpose of this is so I can email the reports without the (very large) underlying data files. This is the code I have so far:
ActiveWorkbook.ActiveSheet.Copy 'NB: Problem when cells have more than 255 characters ActiveWorkbook.ActiveSheet.Unprotect If Not IsEmpty(ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)) Then For Each linkName In ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks) ActiveWorkbook.BreakLink Name:=linkName, Type:=xlLinkTypeExcelLinks Next End If ActiveSheet.Protect
This works fine for the spreadsheet reports, and the charts where the series are hard linked to specific ranges. However, some of the charts use dynamic ranges and it is when exporting these the macro trips up. It converts the ranges in the series formulae to (non-existent!) equivalent ranges in the newly created workbook, as opposed to hard-coding the data values into the series formulae. Anyone any ideas how to accomplish this?
Thanks in advance, Dzinja