Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > What is the code for the active worksheet?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default 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
Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
E-mailing the active worksheet GrahamB Excel Worksheet Functions 5 February 21st 07 09:48 PM
name an active worksheet djshides via OfficeKB.com Excel Worksheet Functions 1 September 29th 06 12:17 AM
How to Add Rows From Active Cell with in a XLL Code Sheraz Excel Worksheet Functions 0 August 8th 06 01:17 PM
how can I get the name of the active worksheet? Mark Dvorkin Excel Worksheet Functions 3 November 4th 05 02:46 AM
email active worksheet es Excel Discussion (Misc queries) 2 January 31st 05 02:59 PM


All times are GMT +1. The time now is 07:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement