I have two charts on different sheets. I need to copy properties of one chart to another without copy and past.
Name, datasourse, title, legends etc.
I have two charts on different sheets. I need to copy properties of one chart to another without copy and past.
Name, datasourse, title, legends etc.
What's different about the charts that precludes copy-paste?
Would copy-paste special formats help?
Otherwise you're stuck writing a tedious routine that makes each property of the second chart the same as that of the first.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Yes, I need this. Can you please show me few examples how to extract property data and store it it to the chartOriginally Posted by JonPeltier
Thanks
You would set up a couple chart variables, then basically have a string of lines equating one chart's property values to the other's.
[vba]
Dim chtSource As Chart
Dim chtTarget As Chart
Set chtSource = Worksheets("Sheet1").ChartObjects(1).Chart
Set chtTarget = Worksheets("Sheet2").ChartObjects(1).Chart
ChtTarget.Parent.Left = ChtSource.Parent.Left
ChtTarget.Parent.Top = ChtSource.Parent.Top
ChtTarget.Parent.Width = ChtSource.Parent.Width
ChtTarget.Parent.Height = ChtSource.Parent.Height
ChtTarget.SeriesCollection(1).Formula = ChtSource.SeriesCollection(1).Formula
ChtTarget.Axes(xlValue, xlPrimary).MinimumScale = ChtSource.Axes(xlValue, xlPrimary).MinimumScale
[/vba]
Repeated ad nauseum to cover all properteis of all objects within the chart.
Are you sure you can't use copy-paste?
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Yes I'm sure.Originally Posted by JonPeltier
Thanks for your help.
You might find it helpful by using the "macro creator" to display all the parameters you need. This means that you would have to turn on the macro generator, then create the first chart from scratch. Just a thought.
Sid
Perhaps I'm oversimplifying his question, but wouldn't a User-Defined Custom Chart Type work?
Office 2010, Windows 7
goal: to learn the most efficient way
I think the OP is overcomplicating the question. I would have thought Copy / Paste Special - Formats would be the answer.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Thanks to everybody for the help.Originally Posted by JonPeltier
But I still expirience problems.
I have a chart (or sheet of type xlChart). And there are some chartobjects.
I'm trying to copy parameters like this
[vba]Sub rebuild()
Dim s As Chart
Dim s1 As Chart
Dim chtSource As ChartObject
Dim chtTarget As ChartObject
Set s = ThisWorkbook.Sheets("Chart1")
Set s1 = ThisWorkbook.Sheets.Add(Type:=xlChart)
s1.PageSetup.ChartSize = s.PageSetup.ChartSize
s1.PageSetup.Orientation = s.PageSetup.Orientation
s1.PageSetup.BottomMargin = s.PageSetup.BottomMargin
s1.PageSetup.CenterFooter = s.PageSetup.CenterFooter
For Each chtSource In s.ChartObjects
Set chtTarget = s1.ChartObjects.Add(chtSource.Left, chtSource.Top, chtSource.Width, chtSource.Height)
chtSource.Name = chtTarget.Name
chtSource.Placement = chtTarget.Placement
'chtSource.Chart.Axes(xlValue, xlPrimary).MinimumScale = chtTarget.Chart.Axes(xlValue, xlPrimary).MinimumScale
chtTarget.Chart.SeriesCollection(1).Formula = chtSource.Chart.SeriesCollection(1).Formula
Next
s1.Activate
ActiveWindow.Zoom = 75
End Sub[/vba]
but I get aplication-difined or Object-defined error on siriesCollection string
What do I do wrong?
There is a difference between a chart on its own sheet (not on a worksheet) and a chart embedded in a worksheet. The object hierarchy of a chart sheet is:
Workbook
Chart
The hierarchy of an embedded chart is:
Workbook
Sheet
ChartObject
Chart
In both cases the object "Chart" is the same, but you need more levels to get to the chart in an embedded chart. Some of the properties you're having trouble with are properties of the ChartObject, which is the container that holds the embedded chart.
My prior example for the case of chart sheets would be:
[vba]
Dim chtSource As Chart
Dim chtTarget As Chart
Set chtSource = Charts("Chart 1")
Set chtTarget = Charts("Chart 2")
ChtTarget.SeriesCollection(1).Formula = ChtSource.SeriesCollection(1).Formula
ChtTarget.Axes(xlValue, xlPrimary).MinimumScale = ChtSource.Axes(xlValue, xlPrimary).MinimumScale [/vba]
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com
_______
But when I have few charts on their own chartsheet then hierarchy is
workbook - chart - chartobject - chart?