Copy data and charts to another Workbook w/o retaining Link to Original File

[frank_AL]

I have a program that updates monthly to reflect Sales, Margin, Commission and Profitability. I maintain data and charts that reflect a 12 month running total for 36 Account Managers.
See example below (using bogus name).

I initially update and maintain this data on a Worksheet in my program Workbook. From there, the data is copied to individual Workbooks for each Account Manager, to Workbooks for 6 District Managers and then to a company level Workbook.

I am in the process of adding additional functionality to the program (as requested by the customer) and one change is impacting the charts. Currently there are multiple code modules performing this task in each 'copy to' Workbook as data is copied to it. Since I am updating the program, I would like to change this process to create the charts only once on the Worksheet I maintain in my program Workbook.

The problem I'm having, is that I need a way to copy the data and charts from one Workbook to another and have the data range for charts in the new Workbook, use the data in the new Workbook, rather than reflecting a link back to the original Workbook. I have tried copying and inserting rows and simple copy and paste. For now I've only tested doing this manually and not with VBA. I've tried Googling the issue but have not found a solution.

Any help with this problem would be much appreciated.

 

[Giordano Bruno]

I came across this issue recently and found that the solution was to de-link the chart from the data so that all data was contained within the chart itself. Below is the subroutine I used. Sorry about the missing indents, I've forgotten how to do this.
Sub Delink()
'>Called by subIsolateCharts
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
'>Convert X Values to arrays of values
srs.XValues = srs.XValues
'>Convert Y Values to arrays of values
srs.Values = srs.Values
'>Convert series name to text
srs.Name = srs.Name
'>Convert Axis title to text on charts 1 and 2
If Right(ActiveChart.Name, 1) <> "3" Then
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Caption = Selection.Caption
End If
Next srs
End Sub

 

[frank_AL]

I came across this issue recently and found that the solution was to de-link the chart from the data so that all data was contained within the chart itself. Below is the subroutine I used. Sorry about the missing indents, I've forgotten how to do this.
Sub Delink()
'>Called by subIsolateCharts
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
'>Convert X Values to arrays of values
srs.XValues = srs.XValues
'>Convert Y Values to arrays of values
srs.Values = srs.Values
'>Convert series name to text
srs.Name = srs.Name
'>Convert Axis title to text on charts 1 and 2
If Right(ActiveChart.Name, 1) <> "3" Then
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Caption = Selection.Caption
End If
Next srs
End Sub

Thank you for your response. Do I call this routine after the Chart is built? Let me know, thanks.

 

[frank_AL]

Thank you for your response. Do I call this routine after the Chart is built? Let me know, thanks.

By searching on 'Delink' chart I found the routine you added along with some others with an explanation. I now understand this routine is run after the Charts are built. I created a simple code module that would loop through all charts and then execute the code to convert the data.

Option Explicit

Sub LoopThroughCharts()
'PURPOSE: Loop through every graph in the active workbook
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Dim srs As Series

'Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
  For Each cht In sht.ChartObjects
    cht.Activate
    For Each srs In cht.SeriesCollection
        srs.Values = srs.Values
    Next
  Next cht
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

However I get an error message on the line of code "For Each srs In cht.SeriesCollection". The error message is "Object doesn't support property or method". I can see that the first Chart is selected along with the data for the Chart when this occurs.

Any idea why I'm getting this error message?

Well, I found the solution. I changed that line of code to be: "For Each srs In ActiveChart.SeriesCollection" and that cleared the error.

 

[frank_AL]

By searching on 'Delink' chart I found the routine you added along with some others with an explanation. I now understand this routine is run after the Charts are built. I created a simple code module that would loop through all charts and then execute the code to convert the data.

Option Explicit

Sub LoopThroughCharts()
'PURPOSE: Loop through every graph in the active workbook
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Dim srs As Series

'Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
  For Each cht In sht.ChartObjects
    cht.Activate
    For Each srs In cht.SeriesCollection
        srs.Values = srs.Values
    Next
  Next cht
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

However I get an error message on the line of code "For Each srs In cht.SeriesCollection". The error message is "Object doesn't support property or method". I can see that the first Chart is selected along with the data for the Chart when this occurs.

Any idea why I'm getting this error message?

Well, I found the solution. I changed that line of code to be: "For Each srs In ActiveChart.SeriesCollection" and that cleared the error.

So this is the code I ended up with. Initially the formatting of the Axis dates had reverted to the number that Excel stores without the desired date format.
I added code to ensure the Axis was formatted as 'mmm-yy' as desired.

Option Explicit

Sub LoopThroughCharts()

Dim sht As Worksheet
Dim cht As ChartObject
Dim srs As Series
 
Application.ScreenUpdating = False
Application.EnableEvents = False

Set sht = ActiveSheet

  For Each cht In sht.ChartObjects
    cht.Activate
    For Each srs In ActiveChart.SeriesCollection
        ' Convert X Values to arrays of values
        srs.XValues = srs.XValues
        ' Convert Y Values to arrays of values
        srs.Values = srs.Values
        ' Convert series name to text
        srs.Name = srs.Name
    Next
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.NumberFormat = "m/d/yyyy"
    Selection.TickLabels.NumberFormat = "[$-en-US]mmm-yy;@"
  Next cht

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

THANK YOU Giordano for getting me pointed in the right direction!

 

[Giordano Bruno]

Thanks for the feedback frank, I've had so much help from this forum and I'm always happy when I can repay a part of the debt.