excel 2007 macro need to have variable range of rows rather than fixed range

I recorded the below macro, used to manipulate and graph 5 sets of data, and it does what i want with the exception of 2 things:

1. The macro is currently used to graph data from a fixed range of cells. In reality, the number of rows will vary from data set to data set, which will either cause a bunch of 0,0 coordinates to plot on the chart or not capture all of the necessary data causing my chart to be incomplete. I would like to have the macro capture only the range that contains the data.

2. The x axis title does not appear on my charts when i run the macro...

also, the keyboard shortcut doesnt seem to work, but as i can just select and run the macro it's really a non-issue

thanks for your help

Jeff

Sub graphing()
'
' graphing Macro
' complete graphing for MTS data
'
' Keyboard Shortcut: Ctrl+a
'
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("O:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("C:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5:B5").Select
    Selection.Copy
    Range("C5").Select
    ActiveSheet.Paste
    Range("C6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=-RC[-2]"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "=-RC[-2]"
    Range("C5:D6").Select
    Selection.Copy
    Range("L5").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Range("U5").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range("AD5").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.SmallScroll ToRight:=4
    Range("AM5").Select
    ActiveSheet.Paste
    Range("AM6:AN6").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("AM6:AN1000")
    Range("AM6:AN1000").Select
    Range("AD6:AE6").Select
    Selection.AutoFill Destination:=Range("AD6:AE1000")
    Range("AD6:AE1000").Select
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    Range("U6:V6").Select
    Selection.AutoFill Destination:=Range("U6:V1000")
    Range("U6:V1000").Select
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    Range("L6:M6").Select
    Selection.AutoFill Destination:=Range("L6:M1000")
    Range("L6:M1000").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("C6:D6").Select
    Selection.AutoFill Destination:=Range("C6:D1000")
    Range("C6:D1000").Select
    Range("E2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
        "C:\Users\JJWIBL\AppData\Roaming\Microsoft\Templates\Charts\std load defl.crtx" _
        )
    ActiveChart.ChartTitle.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartTitle.Text = "RT Pellet Crush at 0.05ipm"
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Displacement (mm)"
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Force (N)"
    Range("E6").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Copy
    Range("J11").Select
    ActiveSheet.Paste
    Range("S11").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range("AB11").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    Range("AK12").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Range("E8").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Name = "=""Test Data"""
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$C$6:$C$1000"
    ActiveChart.SeriesCollection(1).Values = "='" & ActiveSheet.Name & "'!$D$6:$D$1000"
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).Name = "=""Test Data"""
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$L$6:$L$1000"
    ActiveChart.SeriesCollection(1).Values = "='" & ActiveSheet.Name & "'!$M$6:$M$1000"
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.SmallScroll Down:=-6
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Name = "=""Test Data"""
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$U$6:$U$1000"
    ActiveChart.SeriesCollection(1).Values = "='" & ActiveSheet.Name & "'!$V$6:$V$1000"
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    Range("AG8").Select
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.SeriesCollection(1).Name = "=""Test Data"""
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$AD$6:$AD$1000"
    ActiveChart.SeriesCollection(1).Values = "='" & ActiveSheet.Name & "'!$AE$6:$AE$1000"
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SmallScroll ToRight:=3
    Range("AO8").Select
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.SeriesCollection(1).Name = "=""Test Data"""
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$AM$6:$AM$1000"
    ActiveChart.SeriesCollection(1).XValues = "='" & ActiveSheet.Name & "'!$AM$6:$AM$1000"
    ActiveChart.SeriesCollection(1).Values = "='" & ActiveSheet.Name & "'!$AN$6:$AN$1000"
End Sub