VBA: too many charts to copy then format

[Cross Terrier]

I have been asked to copy 45 sheets into a management pack and then save this pack as .xlsx and .pdf

So far so easy, but each sheet has two charts on and something happens when the sheets are copied to change all the format colours when they are pasted into the new workbook.

Since the charts are all separate objects, I can't just select all of the sheets and then correct the formats en masse.

My macro is now too large to run as I have ninety separate sections to correct all the formats.

Can someone please suggest either a way of locking the formats so that the colours do not change when the sheets are copied to a new book with the macro (Excel 2016), or a method to change all of the chart formats at once?


Example (only some relevant sections):

Dim FName As String
FName = Sheets("Control").Range("Q3").Text

Sheets(Array("Sheet1", etc))Copy
Selection.PasteSpecial Paste:=xlPasteVales, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(250, 192, 255)
.TintAndShade = 0
.PatternTintAndShade = 0
End With

ActiveWorkbook.SaveAs Filename: =
"\\tbg.local\users\cross terrier\Monthly reports - " & FName & ".pdf"

 

[mole999]

split the macro into smaller units, and call in sequence. If repeating the same steps on many, look at a way of reusing the code and just changing variables