I added a few lines in the top of the procedure.
Sub Macro2()
Dim rngChartData As Range
On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"mohavv" wrote in message
...
Hi,
I want to create a macro to create a chart of a certain range which
the user has to select.
The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.
When you turn on the recorder following code is part of the VB
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
What I want to know is how you can make the selected range as a
variable to put in the "range" part.
And will I stumble upon more difficulties?
The complete code of the recorded macro is below
Cheers,
Harold
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'
'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub