Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate anonymysing of data in charts
Hi everyone,
Im 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 £. Ive 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. Heres my recorded macro €“ I know there will be lots in it that I dont need, but not confident enough to delete. Sub Anonymise() Range("C14:C29").Select Selection.EntireColumn.Insert Range("D14:D29").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 -- Thank you Annie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate Pasting Charts from Excel to Word | Excel Programming | |||
Automate Data Entry from .xls to IE | Excel Programming | |||
How do I automate moving data when.... | New Users to Excel | |||
automate graph-data | Excel Programming |