I'm still getting the 'argument not optional' compile error, with
..SetSourceData highlighted.

If it helps, the whole of...

Source:=Sheets(MyWorksheet).Range("A1:B501"), _
PlotBy:=xlColumns

is shown in red in the module.

Cheers,

Dan

"Joel" wrote:

I had to make a slight change at the beginning of the macro to select the
worksheet.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+f


For Each MyWorksheet In ThisWorkbook.Worksheets

Worksheets(MyWorksheet.Name).Select

Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets(MyWorksheet).Range("A1:B501"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=MyWorksheet
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveWindow.Visible = False
Windows("Master data.xls").Activate


Next MyWorksheet
End Sub

"Dr Dan" wrote:

Hi Joel,

Thanks for your reply. I knwo how to record a macro but not how to edit one.
The VBA language is foreign to me.

Where in the macro do I need to insert 'oldsheet = ActiveSheet.Name'?

Does it go where the current sheet name is?

Cheers,

Dan

"Joel" wrote:

When you enter the subroutine you could add the statement tto get the sheet
name


oldsheet = ActiveSheet.Name

I've done tthis in similar cases

For Each MyWorksheet In Workbooks(Summary_Spreadsheet).Worksheets
If StrComp(PLOTDATAWORKSHEETNAME, MyWorksheet.Name) = 0 Then

Do something different for sheet 1
Else

Plot data for all other sheets
End If
Next MyWorksheet


"Dr Dan" wrote:

Hi,

I have recorded a macro to plot a chart of the data on a worksheet. I have a
single book with about 100 worksheets each containing two columns of data
that I would like to plot out.

I plan to do it one at a time unless someone can show me the code needed to
automate the process.

My problem is that the macro contains a link to the first sheet (where I
recorded the macro) and so even if I go to a different worksheet and then run
the macro it plots the chart of the first sheet again, and places it on the
first sheet.

In the code below, how do I change the sheet name "20305A.TXT" in both the
source data and location lines to something like 'this worksheet' or
'currently active sheet':

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+f
'
Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("20305A.TXT").Range("A1:B501"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="20305A.TXT"
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveWindow.Visible = False
Windows("Master data.xls").Activate
End Sub

Thanks,

Dan