Macro: "Moving Column" graph

[blk2051]

Hello,

I have an excel macro question. Basically, I need a macro that will allow me to shift the data range of a column graph down 1 row. Each time I run the macro, I want the graph to shift its data range down another row.

Here is a little background:

I have a large amount of numerical data (6750 rows by 30 columns). Each row represents a different point in time, and each column represents one temperature sensor. As time passes (moving down the rows) , the values (temperatures) of each sensor change. I want to be able to visually note how the temperatures change with time. In the end, I want to be able to hit the macro and watch the 30 bars move up and down, hit the macro again, watch the bars move,...,...,... until I hit the end of the data.

Please let me know if I’m explaining this well enough! I appreciate the help!

Thanks!

 

[msaiyad]

Sub GraphAddToStyleSheet02()
' You have to manually create graph as the macros do not support the custom graphs
Dim TerritoryList
Dim DataOfChart01
Dim DataOfChart02
Dim DataOfChart03
Dim NameOfSheet
Dim ChartSeriesName
NameOfSheet = ActiveSheet.Name
If ActiveSheet.Name = "Cpt" And ActiveCell.Address = "$C$21" Then
ActiveWorkbook.Names.Add Name:="ChartTitleStyles", RefersToR1C1:="=OFFSET(RC3:RC4,0,0)"

ActiveWorkbook.Names.Add Name:="ChartDataStyleIntervels01", RefersToR1C1:= _
"=OFFSET(RC3,0,16,1,1),OFFSET(RC3,0,20,1,1),OFFSET(RC3,0,24,1,1),OFFSET(RC3,0,28,1,1),OFFSET(RC3,0,32,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleIntervels02", RefersToR1C1:= _
"=OFFSET(RC3,0,36,1,1),OFFSET(RC3,0,40,1,1),OFFSET(RC3,0,44,1,1),OFFSET(RC3,0,48,1,1),OFFSET(RC3,0,52,1,1)"

ActiveWorkbook.Names.Add Name:="ChartDataStyleIntervels03", RefersToR1C1:= _
"=OFFSET(RC3,0,56,1,1),OFFSET(RC3,0,60,1,1),OFFSET(RC3,0,64,1,1),OFFSET(RC3,0,68,1,1),OFFSET(RC3,0,72,1,1)"


ActiveWorkbook.Names.Add Name:="ChartDataStyleSldPer01", RefersToR1C1:= _
"=OFFSET(RC3,0,17,1,1),OFFSET(RC3,0,21,1,1),OFFSET(RC3,0,25,1,1),OFFSET(RC3,0,29,1,1),OFFSET(RC3,0,33,1,1)"
ActiveWorkbook.Names.Add Name:="ChartDataStyleSldPer02", RefersToR1C1:= _
"=OFFSET(RC3,0,37,1,1),OFFSET(RC3,0,41,1,1),OFFSET(RC3,0,45,1,1),OFFSET(RC3,0,49,1,1),OFFSET(RC3,0,53,1,1)"

ActiveWorkbook.Names.Add Name:="ChartDataStyleSldPer03", RefersToR1C1:= _
"=OFFSET(RC3,0,57,1,1),OFFSET(RC3,0,61,1,1),OFFSET(RC3,0,65,1,1),OFFSET(RC3,0,69,1,1),OFFSET(RC3,0,73,1,1)"


DataOfChart01 = "=('" & ActiveWorkbook.Name & "'!" & "ChartDataStyleIntervels01" & " ,'" & ActiveWorkbook.Name & "'!" & "ChartDataStyleIntervels02" & " ,'" & ActiveWorkbook.Name & "'!" & "ChartDataStyleIntervels03)"
DataOfChart02 = "=('" & ActiveWorkbook.Name & "'!" & "ChartDataStyleSldPer01" & " ,'" & ActiveWorkbook.Name & "'!" & "ChartDataStyleSldPer02" & " ,'" & ActiveWorkbook.Name & "'!" & "ChartDataStyleSldPer03)"
ChartSeriesName = "='" & ActiveWorkbook.Name & "'!" & "ChartTitleStyles"
Charts.Add

' ActiveChart.ChartType = xlLineMarkers
' ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"=(Cpt!R20C19,Cpt!R20C23,Cpt!R20C27,Cpt!R20C31,Cpt!R20C35,Cpt!R20C39,Cpt!R20C43,Cpt!R20C47,Cpt!R20C51,Cpt!R20C55,Cpt!R20C59,Cpt!R20C63,Cpt!R20C67,Cpt!R20C71,Cpt!R20C75)"
ActiveChart.SeriesCollection(1).Name = ChartSeriesName
ActiveChart.SeriesCollection(1).Values = DataOfChart01
' ActiveChart.SeriesCollection(2).XValues = _
"=(Style!R19C19,Style!R19C23,Style!R19C27,Style!R19C31,Style!R19C35,Style!R19C39,Style!R19C43,Style!R19C47,Style!R19C51,Style!R19C55)"
ActiveChart.SeriesCollection(2).Name = ChartSeriesName
ActiveChart.SeriesCollection(2).Values = DataOfChart02

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(3).Select
Selection.Delete
With ActiveChart.SeriesCollection(1).DataLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = False
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
' ActiveChart.Axes(xlCategory, xlSecondary).CategoryType = xlCategoryScale

End If
End Sub

try this