PDA

View Full Version : Copying chart parameters from one chart to another



vosmerkin
04-05-2007, 03:28 AM
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.

JonPeltier
04-05-2007, 05:02 AM
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.

vosmerkin
04-05-2007, 05:37 AM
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.

Yes, I need this. Can you please show me few examples how to extract property data and store it it to the chart
Thanks

JonPeltier
04-05-2007, 06:01 AM
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.


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

Repeated ad nauseum to cover all properteis of all objects within the chart.

Are you sure you can't use copy-paste?

vosmerkin
04-05-2007, 06:08 PM
Are you sure you can't use copy-paste?

Yes I'm sure.
Thanks for your help.

Cyberdude
04-06-2007, 11:35 AM
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

TrippyTom
04-09-2007, 04:35 PM
Perhaps I'm oversimplifying his question, but wouldn't a User-Defined Custom Chart Type work?

JonPeltier
04-09-2007, 08:29 PM
I think the OP is overcomplicating the question. I would have thought Copy / Paste Special - Formats would be the answer.

vosmerkin
04-16-2007, 01:14 AM
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.


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
Repeated ad nauseum to cover all properteis of all objects within the chart.

Are you sure you can't use copy-paste?
Thanks to everybody for the help.
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

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
but I get aplication-difined or Object-defined error on siriesCollection string
What do I do wrong?

JonPeltier
04-16-2007, 05:16 AM
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:



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

vosmerkin
04-16-2007, 06:27 AM
But when I have few charts on their own chartsheet then hierarchy is
workbook - chart - chartobject - chart?