Macro for Graphs

**wm238702** · 04-10-2009, 02:24 PM

Hi all.

I'm new to this. I'm needing to create 6 graphs (line) on numerous worksheets and all of my data comes from the same ranges in each. Every page is formatted identically. When I run a Macro, it uses the data from the page that I created the macro on and doesn't automatically pull the data from the sheet I run it on, like I'd like it to. Can anyone help??? What do I need to do in the macro so it uses the current sheet rather than the original?

Here's the macro I am running now (this would be what I used to create graphs for sheet 44035...but when I run the macro in 44039, it still uses 44035 data...:

Sub Graphs()
'
' Graphs Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Range("A3:F8").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'44035'!$A$3:$F$8")
    ActiveChart.ChartType = xlLineMarkers
    Range("A18:F23").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'44035'!$A$18:$F$23")
    ActiveChart.ChartType = xlLineMarkers
    Range("A3:F3,A10:F14").Select
    Range("A10").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'44035'!$A$3:$F$3,'44035'!$A$10:$F$14")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.ScrollColumn = 1
    Range("A18:F18,A25:F29").Select
    Range("A25").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'44035'!$A$18:$F$18,'44035'!$A$25:$F$29")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.SmallScroll Down:=-18
    Range("A3:F8,A10:F14").Select
    Range("A10").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'44035'!$A$3:$F$8,'44035'!$A$10:$F$14")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A18:F23,A25:F29").Select
    Range("A25").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'44035'!$A$18:$F$23,'44035'!$A$25:$F$29")
    ActiveChart.ChartType = xlLineMarkers
End Sub

I've attached the file. The data for the next set of graphs is on sheet 44037.

**mrice** · 04-11-2009, 04:48 AM

I've removed the reference to the creating sheet so this should now work on any of the sheets.

Sub Graphs()
'
' Graphs Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Range("A3:F8").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("$A$3:$F$8")
    ActiveChart.ChartType = xlLineMarkers
    Range("A18:F23").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("$A$18:$F$23")
    ActiveChart.ChartType = xlLineMarkers
    Range("A3:F3,A10:F14").Select
    Range("A10").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "$A$3:$F$3,$A$10:$F$14")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.ScrollColumn = 1
    Range("A18:F18,A25:F29").Select
    Range("A25").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "$A$18:$F$18,$A$25:$F$29")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.SmallScroll Down:=-18
    Range("A3:F8,A10:F14").Select
    Range("A10").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "$A$3:$F$8,$A$10:$F$14")
    ActiveChart.ChartType = xlLineMarkers
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A18:F23,A25:F29").Select
    Range("A25").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "$A$18:$F$23,$A$25:$F$29")
    ActiveChart.ChartType = xlLineMarkers
End Sub

A logical extension to this would be to put the above code into a loop so that the graphs in all sheets can be generated in one action.