LukeMorris
New Member
- Joined
- Jan 23, 2006
- Messages
- 13
I want a macro that inserts a graph into a sheet that the user defines the name of.
So far I have this code:
It works okay, up to the point of the graph being put in and then it makes a sheet called "CurrentSheetName"
Because this line is incorrect I guess:
But I'm not sure of the correct code, or whether this is even the right way to go about this.
Can anyone help me with this?
Thanks!
Luke
So far I have this code:
Code:
Sub GraphIt()
'
' GraphIt Macro
' Macro recorded 26/01/2006 by Luke Morris
'
'
'Add New Sheet
Sheets.Add
'Make sure the name is valid
On Error Resume Next
'Get the new name
ActiveSheet.Name = InputBox("Name for graph?")
'Keep asking for name if name is invalid
Do Until Err.Number = 0
Err.Clear
ActiveSheet.Name = InputBox("Try Again!" _
& vbCrLf & "Invalid Name or Name Already Exists" _
& vbCrLf & "Please name the New Sheet")
Loop
On Error GoTo 0
'Remember Sheet
Dim CurrentSheetName As String
CurrentSheetName = ActiveSheet.Name
'Make Graph
Sheets("Log Sheet").Select
Range("B10:B39,J10:J39").Select
Range("J10").Activate
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Log Sheet").Range("B10:B39,J10:J39" _
), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=("CurrentSheetName")
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Cash Flow"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Balance"
End With
Sheets("Log Sheet").Select
Range("A1").Activate
End Sub
It works okay, up to the point of the graph being put in and then it makes a sheet called "CurrentSheetName"
Because this line is incorrect I guess:
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=("CurrentSheetName")
But I'm not sure of the correct code, or whether this is even the right way to go about this.
Can anyone help me with this?
Thanks!
Luke