Graph question

[Edwin Merced]

I have a macro that graphs data. I need to change in the macro the sheet
from which Im graphing every time I graph with the macro. Is there an easier
way
These are some of the command lines

ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("D6:G10")

ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("D6:G10"), PlotBy _
:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"

 

[Don Guillett]

try

Use a variable
dim mysheet as string
mysheet=Sheet3
ActiveChart.SetSourceData Source:=Sheets(mysheet).Range("D6:G10")

 

[Don Guillett]

Oops
forgot the " "
mysheet="Sheet3"

 

[Debra Dalgleish]

If you want to be prompted for the sheet name each time you run the
macro, use code similar to the following:

'=========================================
Sub ChartCreatePrompt()
Dim shName As String
shName = Application.InputBox("Enter the Sheet Name", "Sheet Name")
Dim ws As Worksheet
Set ws = Sheets(shName)
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=ws.Range("D6:G10")
.Location Where:=xlLocationAsObject, Name:=shName
End With
ActiveChart.Parent.Name = "MyChart2"
End Sub
'=======================================