Alright, so here is my delimma, I have 100 worksheets in a workbook. Everyother sheet contains data, each of these data sheets contains exactly the same columns rows,, headings, etc, formatted the same and all that. In the empy sheets next to these data sheets (once again, every other one) I need to chart said data. The range for the charts will be the same on each data sheet, the charts will represent the same type of data, with the same lables, same colors, everything. the only thing that changes from data sheet to data sheet and from chart to chart is the heading (the well name) and the actual data. So, instead of using the chart wizard, 50 some odd times, I wanted to make a macro that would make my chart. So what I did was to record the above macro. I recorded it making the first chart exactly how I needed it to appear on all of the sheets, using the first data set as an example. But...then when I run the macro for the other sheets, it of course recorded my "exact" key strokes, so it just remakes the exact same chart in the same worksheet with the same data as before, (obviously because this is what a macro does, but...). So what I am looking for is a way to modify this macro above to say for example, in lay terms...."put the chart in the active worksheet i.e. the one with my cursor in it...not "Sheet3" as above, and pull the data from the range mentioned above, but from the "active worksheet + 1" or the worksheet directly after the one with my cursor in it....this way, I can run the macro on each of my "blank pages" and it will make my chart, pulling data from the page to the right...basically, the above macro, but with, variable references for the data instead of a fixed one.

I hope that makes sense...i am sure that anyone who knows a little about VB could come in and just type some code in there and make it work perfect, but I have been all over the internet reading and asking everyone in the office, and no one knows anything. So here's my last chance before i just bite the bullet and pull up my chart wizard and a pillow!!!

here's the code......


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/10/2006 by JStevens
'
' Keyboard Shortcut: Ctrl+a
'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1") (need this one variable as "active sheet")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Bishop 1-17'!R2C2:R32C2" (need this one variable as well "active sheet+1")
ActiveChart.SeriesCollection(1).Values = "='Bishop 1-17'!R2C7:R32C7"
ActiveChart.SeriesCollection(1).Name = "=""MCF/D"""
ActiveChart.SeriesCollection(2).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(2).Values = "='Bishop 1-17'!R2C4:R32C4"
ActiveChart.SeriesCollection(2).Name = "=""Static"""
ActiveChart.SeriesCollection(3).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(3).Values = "='Bishop 1-17'!R2C5:R32C5"
ActiveChart.SeriesCollection(3).Name = "=""Diff."""
ActiveChart.SeriesCollection(4).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(4).Values = "='Bishop 1-17'!R2C8:R32C8"
ActiveChart.SeriesCollection(4).Name = "=""Csng PSI"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" (this is probably some key problem)
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Bishop 1-17 Monthly Production Data"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
Windows("Production Charts.wells.baca.xls").Activate
Range("D14").Select
End Sub