On May 24, 4:57*am, "Jon Peltier"
wrote:
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- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold