VBA to copy chart line color to many charts in different worksheets

Question

At work I have 72 Excel 2010 workbooks in total, each with 12 sheets, with a chart on each sheet (I think this means the charts are not embedded?). I am a basic programmer having only covered VB at A-Level.

I need all charts (on the 12 seperate sheets) in a workbook to have the same coloured data lines as the first chart in that workbook.
My initial thoughts were to record a macro of me manually changing the line colours, thicknesses and so on then view the code for this macro and put some sort of loop around it.

After many hours trying different suggestions and many google searches I can't get it to work.

The code I have so far is as follows:

Sub Macro1()

Dim i As Integer
Dim sht As Worksheet

For i = 1 To ActiveWorkbook.Worksheets.Count
Set sht = ActiveWorkbook.Sheets(i)

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(1).Select
ActiveChart.SeriesCollection(1).Select
With Selection
    .MarkerStyle = 2
    .MarkerSize = 7
End With
Selection.MarkerStyle = -4168
Selection.Format.Fill.Visible = msoFalse
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .Weight = 1.25
End With

ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(2).Select
ActiveChart.SeriesCollection(2).Select
With Selection
    .MarkerStyle = 1
    .MarkerSize = 7
End With
Selection.MarkerStyle = -4168
Selection.Format.Fill.Visible = msoFalse
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 112, 192)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 112, 192)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 112, 192)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .Weight = 1.25
End With

ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(3).Select
ActiveChart.SeriesCollection(3).Select
With Selection
    .MarkerStyle = 3
    .MarkerSize = 7
End With
Selection.MarkerStyle = -4168
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent1
    .ForeColor.TintAndShade = 0
'     .ForeColor.Brightness = 0
    .Solid
End With
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
    .Solid
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 112, 192)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .Weight = 1.25
End With
Selection.Format.Fill.Visible = msoFalse

ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(4).Select
ActiveChart.SeriesCollection(4).Select
With Selection
    .MarkerStyle = -4168
    .MarkerSize = 7
End With
Selection.Format.Fill.Visible = msoFalse
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(112, 48, 160)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(112, 48, 160)
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(112, 48, 160)
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .Weight = 1.25
End With


Next i


End Sub

This code runs and does what I want but only on the worksheet you actually have open in excel, it will not run through and run the macro on each worksheet in the workbook. Any ideas?

Thanks in advance

Jos Vinke Jos Vinke · Accepted Answer · 2013-11-01T10:56:59

You could call your Sub Macro1 from a loop that loops through all Worksheets.

For example:

Sub WorksheetLoop()

     ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets

        ' Insert your code here.
        ' This line displays the worksheet name in a message box.
        MsgBox Current.Name
     Next

End Sub

Then you can pass the Current Worksheet to your Function and run your code on that sheet. For more info see: Macro to Loop Through All Worksheets in a Workbook

In this case you would change your code like this:

Sub Macro1(Byval Current As Worksheet)

    Dim i As Integer
    Dim sht As Worksheet

    For i = 1 To ActiveWorkbook.Worksheets.Count
    Set sht = Current

    sht.ChartObjects("Chart 1").Activate

    .....

End Sub

And create a loop like this:

Sub WorksheetLoop()

     Dim Current As Worksheet

     For Each Current In Worksheets
        Call Macro1(Current)
     Next

End Sub

VBA to copy chart line color to many charts in different worksheets

1 Answers