Dynamic Graph Arguement

[phnar]

Hello, I have a graph on sheet1 that instances several sheet1 series. I want
to copy and paste that graph onto sheet 2, and have it instance the same
cells, except in sheet 2. I need to do this for quite a few sheets. Is there
a way I can have Excel automatically pick the corresponding sheet and fill in
the appropriate graph arguement?

Cheers

 

[Tushar Mehta]

Select the original source of all the charts before running
changeChartReferencesOnAllSheets. This will change all references in
all charts in all worksheets (except the original source sheet) from
the original source sheet to the parent sheet of the chart(object).

The code is lightly tested.

Option Explicit

Sub changeReferencesOnOneChart(ByRef whatChart As Chart, _
ByVal SrcSheetName As String, ByVal NewName As String)
Dim I As Integer
With whatChart
For I = 1 To .SeriesCollection.Count
With .SeriesCollection(I)
.Formula = Replace(.Formula, _
SrcSheetName & "!", "'" & NewName & "'!")
.Formula = Replace(.Formula, _
"'" & SrcSheetName & "'!", "'" & NewName & "'!")
End With
Next I
End With
End Sub
Sub changeChartReferenceOnOneSheet(ByVal SrcSheetName As String, _
ByRef aWS As Worksheet)
Dim J As Integer
If SrcSheetName = aWS.Name Then Exit Sub
For J = 1 To aWS.ChartObjects.Count
changeReferencesOnOneChart aWS.ChartObjects(J).Chart, _
SrcSheetName, aWS.Name
Next J
End Sub
Sub changeChartReferencesOnAllSheets()
'activate the *original* source for all charts before running _
this procedure
Dim aSheet As Object, SrcName As String
SrcName = ActiveSheet.Name
For Each aSheet In ActiveWorkbook.Sheets
If TypeOf aSheet Is Worksheet Then
changeChartReferenceOnOneSheet SrcName, aSheet
End If
Next aSheet
End Sub
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Copy the entire sheet, with the data and chart, so you have a duplicate
sheet with a chart that refers to the data on this duplicate sheet. Now
copy the new data, and paste it on top of the original data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______