Peter T,

Thanks for the offer. I have a stripped down version in a zip file. Please
advise as to how I can xfer this to you.

Regards,
Joe

"Peter T" wrote:

I encountered a new error on line "Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")"

You'd need to change "Book1" and "Sheet1" to suit. Eg, change "Book1" to
"Book1.xls" if you had saved the workbook.

Run-time error '-2147352565 (8002000b)':

There are one or two scenarios that trigger this error with charts in 2007,
some of which are new to SP2.

If you want to send a stripped down version of your file (zipped), just
enough to replicate the problem, I'll have a look tomorrow. Please include
full details of what to do if not obvious.

Regards,
Peter T


"Joe" wrote in message
...
It errors on oFrom.Copy After:=oTo. I tried running your example,
however,
it couldn't find Chart1 (or Chart 1) in Book1 even though I was looking at
the chart while processing.

What I found interesting about this example was when I saved Book1 as
Book1.xls and ran the marco I encountered a new error on line "Set ws1 =
Workbooks("Book1").Worksheets("Sheet1")"

Run-time error '-2147352565 (8002000b)':

The specified dimension is not valid for the current chart type.

Regards,
Joe


"Peter T" wrote:

Set oFrom = oChartSkel.Sheets(x)
Set oTo = oThisMgrChartBook.Sheets(1)
debug.? oFrom.Name, oTo.Name

Which of the above lines gave you the error?
Did the demo I posted earlier work?

Regards,
Peter T


"Joe" wrote in message
...
I did as suggest and encountered the "Automation Error" as well.
Interesting enough, if I remove the chart objects in ChartSkel.xls then
the
marco will process without incident.

It would be nice if I could attached the ChartSkel.xls to this post.

Your assistance in zeroing in on the line of code that I believe is the
culprit is greatly appreciated.

"Peter T" wrote:

I can only glance at your code but on the face of it there's nothing
wrong.

oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

the above is in effect the same as

cht1.Copy after:=ws2

IOW, no problem with method (I assume you tried it?)

In your own code add a bit more to isiloate the problem

dim oFrom as object, oTo as Object

'in the loop
Set oFrom = oChartSkel.Sheets(x)
Set oTo = oThisMgrChartBook.Sheets(1)
debug.? oFrom.Name, oTo.Name

oFrom.Copy After:=oTo

Regards,
Peter T




"Joe" wrote in message
...
Peter T,

Thank you again. The copy/paste approach I took is similar to
yours;
however, I created a majority of the code using the macro recorder;
your
code
appears to be more efficient.

ChartSkel.xls (source) can be looked at as my chart template. This
workbook
has two tabs with charts and a data table/grid: one tab is for
"Workforce"
charting and the other is for charting "Earned Value". For each
Cost
Account
that I process the macro will create two tabs within the workbook.
For
example, at the end of my process I rename Book1.xls (target) to the
Project/Manager Name and within this workbook I will have the cost
account
charts: Sheet1 has been renamed to "1.1 Workforce", Sheet2 is now
"1.1
EarnedValue", Sheet3 is now "1.2 Workforce" and so on.

The code always fails on this line: oChartSkel.Sheets(x).Copy
After:=oThisMgrChartBook.Sheets(1)

I hate to say it but I think this might be a bug within Excel 2007.
Maybe
you could confirm this by using the above line of code in your test
macro?

Do
For x = 1 To oChartSkel.Sheets.Count
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)
' processing code omitted
Next
Loop Until rngTestRange.Row = lngLastRow

Whe
oChartsSkel = ChartSkel.xls
oThisMgrChartBook = Book1.xls

At this point I am going forward with coding around this one line.
Yes, I
would be willing to try your ComAddin, however, I will be away for a
couple
of weeks starting Monday. Thank you once again.

Regards,
Joe

"Peter T" wrote:

If I follow, in effect you wan to duplicate a chart into a second
workbook,
right?

As you've also figured there are two approaches,
remake the chart from scratch
or copy the chart and "resource" to similar data (itself perhaps
copied
from
the source wb) in the new workbook.

Personally I probably prefer to recreate a new chart, however
copying
is
certainly much easier and typically should work fine, assuming of
course
you
know the location of the original data.

oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

not sure why,

I made simple chart with two series,
B1:C2: series1 & 2 names
A2:A4 x values
B2:C4 Y values for the two series
the data is on Sheet1

the original chart is in "Book1", in a chartsheet named "Chart1"

Following will copy the source data to "Sheet1" in "Book2" and the
chart
to
a chart-sheet in Book2, linked to the newly pasted data in
Book2!Sheet1

Sub CopyChartAndData()
Dim p1 As Long, p2 As Long
Dim sF As String, sRep As String, sSht As String
Dim rng1 As Range, rng2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cht1 As Chart
Dim cht2 As Chart
Dim sr As Series

Set ws1 = Workbooks("Book1").Worksheets("Sheet1")
Set rng1 = ws1.Range("A1:C4")

Set ws2 = Workbooks("Book2").Worksheets("Sheet1")
Set rng2 = ws2.Range("A1:C4")

rng1.Copy rng2

Set cht1 = Workbooks("Book1").Charts("Chart1")

cht1.Copy after:=ws2

Set cht2 = Workbooks("Book2").Sheets(ws2.Index + 1)

sSht = "'" & ws2.Name & "'"

For Each sr In cht2.SeriesCollection
sF = sr.Formula
p1 = InStr(9, sF, "[")
p2 = InStr(p1, sF, "!")
sRep = Mid$(sF, p1, p2 - p1)
sF = Replace(sF, sRep, sSht)
sr.Formula = sF
Next

End Sub


I suspect your problem with the copy sheet is just some simple
error.
But
did you isolate a particular aspect of code that errors in SP2 but
works
in
other versions.

FWIW, I have quite an extensive ComAddin that removes all links in
charts
to
cells (data goes to named arrays or arrays in formulas). It can
also
dump
source data to a new range cells and optionally relink the dumped
data
(eg
copy chart to new wb, dump source data to new wb, resource to the
dumped
data). It worked very well in earlier versions but there are one or
two
things that have been problematic in Excel 2007 (waiting for a
rainy
day
to
update). Not sure if it would be of any use to you but let me know
if
interested to try the beta.

Regards,
Peter T

"Joe" wrote in message
...
Peter T,

Thank you for the suggestion. In creating a smaller version of
the
macro
I
uncovered the problem I am having with the command:
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)

oChartSkel is a spreadsheet (actual name is ChartSkel.xls) that
contain
line
chart objects (one chart on one worksheet and two on the other).
When
I
remove the line charts from the spreadsheet and re-run the macro
it
completes
without incident. I've tried recreating the chart objects using
Excel
2007
to no avail. So, in the meantime what used to take one line of
code
is
now
being replaced with 57 lines. :( (Long story short over the 57
lines
is
this: I select the range of cells to copy/paste over to my new
workbook.
Then I copy/paste over the Chart object and update the
data-series.
I
guess
it is a good thing there are only two worksheets with a total of
three
charts). Do you know of a way to get around all of this chart
creation/manipulation?

Regards,
Joe