Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dynamic Graph Arguement
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 |
#2
|
|||
|
|||
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 In article , says... 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 |
#3
|
|||
|
|||
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/ _______ phnar wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a combination graph with stackedColumns+lineGraph | Charts and Charting in Excel | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
problem with dynamic graph | Excel Worksheet Functions | |||
Graph Axes | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |