Apply default chart to multiple worksheets?

**frankabe** · 06-28-2009, 09:34 PM

Hi,

I have a chart format that I want to apply to same data format through 100's separate worksheets in one work.

1. Apply custom chart template
2. Change column titles from series 1,2,3,4 to may, apr, mar, feb, oct
3. Change chart titles to coo function for y axis and time for x axis

I have done this already by recording separate marcos but applying it to 100's of worksheets does not work.

Can anyone help?

Best,

Frank

**Jbentley** · 07-03-2009, 05:15 AM

Hi,
Are you able to post an example of say 3 sheets and the code you've recorded as an attached file.

Always easier to work with examples

**frankcase** · 07-17-2009, 01:37 PM

Hi,

Sorry for the delay but here is an example of what i am trying to do. I want to apply this chart template to the data in the worksheets.

**Andy Pope** · 07-18-2009, 07:30 AM

Should your chart not be a xy-scatter rather than a Line chart type?

**frankcase** · 07-18-2009, 10:56 PM

This is the correct file format for this type of dataset :-)

**Andy Pope** · 07-19-2009, 08:16 AM

Here is a routine to duplicate the chart across the sheets.
It should also work with other workbooks, taking the existing chart from the Title sheet and copying it across.

I realise it's your data and your chart but just check the difference in the plots by comparing the copied chart to the existing one on sheet2.

The xy-scatter has true value axis rather than category one that the line provides. I'm sure you would agree that the outcome of the lines is radically different.

**frankcase** · 07-19-2009, 02:26 PM

Hi,

Thank you for the input and the x domain is not linear time but it is a sampling time (i can explain application offline).

The time domain x will be the sample for all the measurements. We will then have different number of replicates. It could be anywhere from 3-15 replicates.

We would like to average the number of replicates, obtain standard deviation, % relative std deviation and plot the chart to the right side of the data.

Here is a file of what we are trying to do with a macro i recorded. If we could automate the data processing side and add the chart component that your provided. We would dramatically reduce our data processing time and help us do some real science :-)

Frank

**Andy Pope** · 07-20-2009, 07:36 AM

Try this code modification for adding 3 calculated columns.

Code:

Sub X()

    Dim chtTemplate As Chart
    Dim shtTemp As Worksheet
    Dim lngLastRow As Long
    Dim chtLocal As Chart
    Dim lngCol As Long
    Dim lngSeries As Long
    Dim objSeries As Series
        
    Set chtTemplate = ThisWorkbook.Worksheets("Title").ChartObjects(1).Chart
    Application.ScreenUpdating = False
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        If shtTemp.Name <> chtTemplate.Parent.Parent.Name Then
            If shtTemp.Cells(4, 1) <> "" Then
                lngLastRow = shtTemp.Cells(shtTemp.Rows.Count, 1).End(xlUp).Row
                With chtTemplate.Parent
                    .Copy
                    shtTemp.Paste
                    Set chtLocal = shtTemp.ChartObjects(shtTemp.ChartObjects.Count).Chart
                    
                    With chtTemplate.Parent
                        chtLocal.Parent.Left = .Left
                        chtLocal.Parent.Top = .Top
                        chtLocal.Parent.Width = .Width
                        chtLocal.Parent.Height = .Height
                    End With
                End With
                
                lngCol = 2
                Do While shtTemp.Cells(4, lngCol) <> ""
                    If lngCol - 1 <= chtTemplate.SeriesCollection.Count Then
                        ' Use existing series
                        Set objSeries = chtLocal.SeriesCollection(lngCol - 1)
                    Else
                        Set objSeries = chtLocal.SeriesCollection.NewSeries
                    End If
                    With objSeries
                        ' update range reference
                        .Name = shtTemp.Cells(4, lngCol)
                        .Values = shtTemp.Range(shtTemp.Cells(5, lngCol), shtTemp.Cells(lngLastRow, lngCol))
                    End With
                    lngCol = lngCol + 1
                Loop
                
                With shtTemp
                    .Cells(4, lngCol).Resize(1, 3) = Array("Average", "Standard Deviation", "% RSD")
                    'Average
                    shtTemp.Cells(5, lngCol).Formula = "=AVERAGE(B5:" & .Cells(5, lngCol - 1).Address(False, False) & ")"
                    ' STDEV
                    shtTemp.Cells(5, lngCol + 1).Formula = "=STDEV(B5:" & .Cells(5, lngCol - 1).Address(False, False) & ")"
                    ' %RSD
                    shtTemp.Cells(5, lngCol + 2).Formula = "=(" & _
                                                                .Cells(5, lngCol + 1).Address(False, False) & "/" & _
                                                                .Cells(5, lngCol).Address(False, False) & ")*100"
                    ' Populate all rows
                    .Range(.Cells(5, lngCol), .Cells(lngLastRow, lngCol + 2)).FillDown
                End With
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub