Automate anonymysing of data in charts

[Annie Whitley]

Hi everyone,

I’m hoping that someone can help me with some code to automate the
anonomysing of data in a chart and then changing bar colours and saving as a
new chart and returning to original chart and doing it again.

Each chart has 14 bars representing spend per budget centre and an
additional 2 bars for average depot spend and overall average company spend.

Data is on a separate sheet

C14:C29 holds budget centre code and D holds costs in £.

I’ve recorded a macro which adds a column to which I move budget centre data
(so costs are now in E) and then copy the data back for the 1 bar of interest
plus the two averages bars.
Then change colours as required and save to separate book.

What I need help with is all the elegant stuff that would return me to the
data and offset to the next set of data down and the next bar over.

Here’s my recorded macro – I know there will be lots in it that I don’t
need, but not confident enough to delete.

Sub Anonymise()

Range("C14:C29").Select
Selection.EntireColumn.Insert
Range("D1429").Select
Selection.Cut
Range("C14").Select
ActiveSheet.Paste
Range("C14").Select
Selection.Copy
Range("D14").Select
ActiveSheet.Paste
Range("C28").Select
Application.CutCopyMode = False
Selection.Copy
Range("D28").Select
ActiveSheet.Paste
Range("C29").Select
Application.CutCopyMode = False
Selection.Copy
Range("D29").Select
ActiveSheet.Paste
Sheets("Chart1").Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
Application.CutCopyMode = False
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(15).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(16).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Sheets("Chart1").Copy
Windows("Spend per budget Centre - Charts.xls").Activate
Sheets("Data").Select
Range("D14").Select
Selection.ClearContents
Range("C15").Select
Selection.Copy
Range("D15").Select
ActiveSheet.Paste
Sheets("Chart1").Select
ActiveChart.SeriesCollection(1).Points(1).Select
ActiveChart.SeriesCollection(1).Points(2).Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
Application.CutCopyMode = False
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Sheets("Chart1").Copy
Windows("Spend per budget Centre - Charts.xls").Activate
Sheets("Data").Select
End Sub

If anyone could help me that would be fantastic