Problems copying and pasting a chart

[bev]

Hi,
I'm hoping someone can help me with this. I'm trying to write a macro
that will select an entire worksheet (in Book1), open a new workbook
(Book2) and paste what I've copied. The problem is, there is a chart
object on the worksheet that reads from 2 columns in the worksheet.
When pasted in Book2, this chart still references the data from Book1.
I really want it to read from the same range of cells but in Book2
instead of Book1. Is this possible? The copied formulas in the
worksheet reference the cells in Book2 just fine.

Thanks,
bev

 

[Pete McCosh]

Bev,

rather than copy and paste, you can ust copy the whole
worksheet which should circumvent the problem:

Workbooks("SourceBook").Worksheets("SourceSheet").copy

This will create a copy of the required sheet in a new
workbook.

Cheers, Pete

 

[bev]

Thanks for your response Pete, but I'm still having some trouble. I'm
getting a runtime error (1004) saying "Copy method of Worksheet class
failed".

Originally, from recordiing the sequence "selectall->copy->new
workbook->paste" as a macro, this is the code that resulted

Cells.Select
Range("I13").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.Range("B31").Select

Trying your method I changed the code to:

Worksheets("Sheet1").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.Range("B31").Select

but this resulted in the error.
Any ideas?
Bev

 

[Dave Peterson]

Just use this line:

Worksheets("Sheet1").Copy

If you don't say where to copy this worksheet, it'll create a brand new workbook
for you.

So you don't need the rest of the stuff.

 

[bev]

Now I see what you meant! No pasting required. Well I still had the
same problem, but I figured out what was wrong. The code was being
executed after a button click, so it was the button that was
'selected' and not a cell on the worksheet. That "copy" method only
works if the sheet is active I guess. So in the code for my button I
had to make sure I selected a cell on the sheet before using the line
Worksheets("Sheet1").Copy.

Thank you very much for getting me on the right path. You guys are
fabulous.

Bev

 

[Dave Peterson]

If you're using xl97, then you can change the .takefocusonclick property for
that button to false (it's a button from the controltoolbox toolbar, right?)

If you ever use a different control from that toolbar (and it doesn't have that
..takefocusonclick property), you can add this line to the top of your procedure:

activecell.activate

(In fact, this extra line will work for the commandbutton, too.)