What is the code for the active worksheet?
 

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

What is the code for the active worksheet?
 

try this code. I simply took the recorded macro and added a for loop that
will loop though every sheet in the workbook

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


For Each MyWorksheet In Workbooks(ThisWorkbook).Worksheets



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

What is the code for the active worksheet?
 

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

What is the code for the active worksheet?
 

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

What is the code for the active worksheet?
 

Looks good Joel, thanks, I'll have a go.

Dan

"Joel" wrote:

try this code. I simply took the recorded macro and added a for loop that
will loop though every sheet in the workbook

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


For Each MyWorksheet In Workbooks(ThisWorkbook).Worksheets



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

What is the code for the active worksheet?
 

Hi Joel,

I'm getting a compile error saying 'argument not optional' and the phrase
'SetSourceData' is highlighted.

When I pasted in the code I also got a compile error with 'expected
expression' and the := between Source and Sheets highlighted.

Thanks,

Dan

"Joel" wrote:

try this code. I simply took the recorded macro and added a for loop that
will loop though every sheet in the workbook

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


For Each MyWorksheet In Workbooks(ThisWorkbook).Worksheets



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

What is the code for the active worksheet?
 

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

What is the code for the active worksheet?
 

I forgot .name in a couple of spots. This should work

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.name).Range("A1:B501"),
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=MyWorksheet.Name
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,

I'm getting a compile error saying 'argument not optional' and the phrase
'SetSourceData' is highlighted.

When I pasted in the code I also got a compile error with 'expected
expression' and the := between Source and Sheets highlighted.

Thanks,

Dan

"Joel" wrote:

try this code. I simply took the recorded macro and added a for loop that
will loop though every sheet in the workbook

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


For Each MyWorksheet In Workbooks(ThisWorkbook).Worksheets



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

What is the code for the active worksheet?
 

Compile error still there.

"Dr Dan" wrote:

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

What is the code for the active worksheet?
 

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

What is the code for the active worksheet?
 

Missed the .name again in the line.

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

"Dr Dan" wrote:

Compile error still there.

"Dr Dan" wrote:

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

What is the code for the active worksheet?
 

It's still getting the same error... here's the latest version I have

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+g
'
For Each MyWorksheet In ThisWorkbook.Worksheets

Worksheets(MyWorksheet.Name).Select

Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets(MyWorksheet.name).Range("A1:B501"),
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=MyWorksheet.Name
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

What is the code for the active worksheet?
 

the lines arre getting wraped. In basic, if instructions is used on more
than one line the continuation character _ (underline) needs to be added to
the end of a line.


Add _ to end of the 1st two lines or make these 3 lines 1 line
-------------------------------------------------------------------------------------

ActiveChart.SetSourceData
Source:=Sheets(MyWorksheet.name).Range("A1:B501"),
PlotBy:=xlColumns

-----------------------------------------------------------------------------------

"Dr Dan" wrote:

It's still getting the same error... here's the latest version I have

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+g
'
For Each MyWorksheet In ThisWorkbook.Worksheets

Worksheets(MyWorksheet.Name).Select

Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

ActiveChart.Location Whe=xlLocationAsObject, Name:=MyWorksheet.Name
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