What is the code for the active worksheet?

D

Dr Dan

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 Where:=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
 
J

Joel

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 Where:=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
 
J

Joel

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 Where:=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
 
J

Joel

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
 
D

Dr Dan

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

Dan

Joel said:
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 Where:=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
Click to expand...
 
D

Dr Dan

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
 
D

Dr Dan

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
 
J

Joel

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 Where:=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
 
D

Dr Dan

Compile error still there.

Dr Dan said:
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
Click to expand...
 
D

Dr Dan

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
 
D

Dr Dan

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 Where:=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
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to specify chart location as obj in current sheet? 6
VB Macro to Create a Chart 0
reverse axis not working as before... 0
VBA Coding Help for Beginner 0
Applying custom chart type 1
Excel inconsistency 4
Code to Create Chart 0
Chart Error when using Chart Line - Column on 2 Axes in vba code 2

Top