Please help me with running this macro in Excel 2007

J

johns_myself

I have written this macro to create a changing chart (based on the
active cell). It is working fine on Excel 2003, but not running on
Excel 2007. Can somebody help?

Sub GraphAddToStyleSheet()
Dim TerritoryList
Dim DataOfChart01
Dim DataOfChart02
Dim DataOfChart03
Dim DataOfChart04
Dim DataOfChart05
Dim NameOfSheet
NameOfSheet = ActiveSheet.Name
If UCase(ActiveSheet.Name) = "STYLE" And ActiveCell.Address = "$D
$21" Then
ActiveWorkbook.Names.Add Name:="ChartTitleStyle",
RefersToR1C1:="=OFFSET(RC4,0,0)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleSoldPercent",
RefersToR1C1:= _

"=OFFSET(RC4,0,17,1,1),OFFSET(RC4,0,29,1,1),OFFSET(RC4,0,41,1,1),OFFSET(RC4,0,53,1,1),OFFSET(RC4,0,65,1,1),OFFSET(RC4,0,77,1,1),OFFSET(RC4,0,89,1,1),OFFSET(RC4,0,101,1,1),OFFSET(RC4,0,113,1,1),OFFSET(RC4,0,122,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStylePO",
RefersToR1C1:= _

"=OFFSET(RC4,0,10,1,1),OFFSET(RC4,0,22,1,1),OFFSET(RC4,0,34,1,1),OFFSET(RC4,0,46,1,1),OFFSET(RC4,0,58,1,1),OFFSET(RC4,0,70,1,1),OFFSET(RC4,0,82,1,1),OFFSET(RC4,0,94,1,1),OFFSET(RC4,0,106,1,1),OFFSET(RC4,0,115,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleGrn",
RefersToR1C1:= _

"=OFFSET(RC4,0,11,1,1),OFFSET(RC4,0,23,1,1),OFFSET(RC4,0,35,1,1),OFFSET(RC4,0,47,1,1),OFFSET(RC4,0,59,1,1),OFFSET(RC4,0,71,1,1),OFFSET(RC4,0,83,1,1),OFFSET(RC4,0,95,1,1),OFFSET(RC4,0,107,1,1),OFFSET(RC4,0,116,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleSld",
RefersToR1C1:= _

"=OFFSET(RC4,0,12,1,1),OFFSET(RC4,0,24,1,1),OFFSET(RC4,0,36,1,1),OFFSET(RC4,0,48,1,1),OFFSET(RC4,0,60,1,1),OFFSET(RC4,0,72,1,1),OFFSET(RC4,0,84,1,1),OFFSET(RC4,0,96,1,1),OFFSET(RC4,0,108,1,1),OFFSET(RC4,0,117,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleMrgn",
RefersToR1C1:= _

"=OFFSET(RC4,0,15,1,1),OFFSET(RC4,0,27,1,1),OFFSET(RC4,0,39,1,1),OFFSET(RC4,0,51,1,1),OFFSET(RC4,0,63,1,1),OFFSET(RC4,0,75,1,1),OFFSET(RC4,0,87,1,1),OFFSET(RC4,0,99,1,1),OFFSET(RC4,0,111,1,1),OFFSET(RC4,0,120,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleAge",
RefersToR1C1:= _

"=OFFSET(RC4,0,8,1,1),OFFSET(RC4,0,20,1,1),OFFSET(RC4,0,32,1,1),OFFSET(RC4,0,44,1,1),OFFSET(RC4,0,56,1,1),OFFSET(RC4,0,68,1,1),OFFSET(RC4,0,80,1,1),OFFSET(RC4,0,92,1,1),OFFSET(RC4,0,104,1,1),OFFSET(RC4,0,114,1,1)"
DataOfChart01 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStyleSoldPercent"
DataOfChart02 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStylePO"
DataOfChart03 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStyleGrn"
DataOfChart04 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStyleSld"
DataOfChart05 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStyleMrgn"
DataOfChart06 = "='" & ActiveWorkbook.Name & "'!" &
"ChartDataStyleAge"
TerritoryList = "='" & ActiveWorkbook.Name & "'!" &
"ChartTitleStyle"
ActiveSheet.Range("G5").Value = "PO"
ActiveSheet.Range("G6").Value = "Grn"
ActiveSheet.Range("G7").Value = "Sld"
ActiveSheet.Range("G8").Value = "Mrgn"
ActiveSheet.Range("G9").Value = "Age"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"=(Style!R19C10,Style!R19C22,Style!R19C34,Style!
R19C46,Style!R19C58,Style!R19C70,Style!R19C82,Style!R19C94,Style!
R19C106,Style!R19C118)"
ActiveChart.SeriesCollection(1).Values = DataOfChart02
ActiveChart.SeriesCollection(1).Name = "=Style!R5C7"
ActiveChart.SeriesCollection(2).Values = DataOfChart03
ActiveChart.SeriesCollection(2).Name = "=Style!R6C7"
ActiveChart.SeriesCollection(3).Values = DataOfChart04
ActiveChart.SeriesCollection(3).Name = "=Style!R7C7"
ActiveChart.SeriesCollection(4).Values = DataOfChart05
ActiveChart.SeriesCollection(4).Name = "=Style!R8C7"
ActiveChart.SeriesCollection(5).Values = DataOfChart06
ActiveChart.SeriesCollection(5).Name = "=Style!R9C7"
ActiveChart.Location Where:=xlLocationAsObject,
Name:=NameOfSheet
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = False
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.Axes(xlValue).Select
Selection.Delete

ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(5).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Interior.ColorIndex = xlNone

ActiveChart.SeriesCollection(6).Select
Selection.Delete
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"=(Style!R19C10,Style!R19C22,Style!R19C34,Style!
R19C46,Style!R19C58,Style!R19C70,Style!R19C82,Style!R19C94,Style!
R19C106,Style!R19C118)"
ActiveChart.SeriesCollection(1).Values = DataOfChart01
ActiveChart.SeriesCollection(1).Name = TerritoryList
ActiveChart.SeriesCollection(1).Select
Selection.Interior.ColorIndex = 1
ActiveChart.Location Where:=xlLocationAsObject,
Name:=NameOfSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
With ActiveChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
ActiveChart.HasLegend = True
ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
HasLeaderLines:=False, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False,
ShowBubbleSize:=False
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.SeriesCollection(2).Select
Selection.Delete
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1.2
.MinorUnitIsAuto = True
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
With ActiveChart.SeriesCollection(1).DataLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
End If
ActiveSheet.Range("$D$21").Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

add a chart in a Add-In 1
Format Data Series in a Pivot Chart 2
reverse axis not working as before... 0
Automate anonymysing of data in charts 0
Macro Compatibility 0
Floating Bar Chart 1004 error 0
Code to Create Chart 0
Run macro on refresh data (pivot table) 0

Top