VB and Excel

[jping45]

Hi all,
Here's the problem. I have created a macro that will create some graphs, however, the filename and title have to be hard coded into the macro. I would like to have the macro use a variable, ie, name of file that is open, inside of the macro where you see "FILE NAME VARAIBLE GOES HERE". Below you will find part of the macro in question...
Any help would be greatly appreciated...

Regards
Jake


Sub SarData()
'
' SarData Macro
' Macro recorded 2/1/02 by Manpower User
'
' Keyboard Shortcut: Ctrl+Shift+S
'

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("FILE NAME VARIABLE GOES HERE&quot.Range( _
"A1:E275&quot, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "FILENAME VARIABLE GOES HERE"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent Utilized"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnit = 1
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
End Sub

 

[CDB]

here is macro again

Sub SarData()
'
' SarData Macro
' Macro recorded 2/1/02 by Manpower User
'
' Keyboard Shortcut: Ctrl+Shift+S
'
name = ActiveWorkbook.Name (this is if you want to put workbook name)
name = ActiveWorkbook.Sheets(1).Name (this is if you want to put worksheets name, see that 1 is the first worksheet on workbook from letf to right)

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(name).Range( _
"A1:E275&quot, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent Utilized"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnit = 1
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
End Sub


let me know if it helps or you need another solution to boscarlos@yahoo.com

 

[jping45]

cdb,

That seems to have worked. Thanks for the tips. One last question.. Do you know of any good resources for VBA?
Regards
Jake