Copying and pasting

[bambam77]

Here is a macro I wrote to copy a form and associated chart and save
them as a new file in a different folder, then erase the original for
for newd ata entry. The completed form copies over fine, but the copied
chart, loses it's data. I am new at progamming and I basically taught
myself. Here is my code for this macro. Let me know what I am doing
wrong.


'
' Erase_n_Archive Macro
' Macro 9/9/2004 written by Vincent Willis
'

Workbooks("HH.16.014 Kiln Heat Up Form.xls").Activate
FileToSave = InputBox("Enter the Date" & _
" = ""Heat Up"" plus the month-day-year" & _
" Example: Heat Up 10-13-2003", _
"Name day to archive")

Worksheets.Add
ActiveSheet.Name = "HEAT UP"
Sheets(" Heat Up").Select
Range("A4:X48").Select
Selection.Copy
Worksheets("HEAT UP").Activate
ActiveSheet.Paste

Sheets("HEAT UP").Copy
ChDrive "I:\"
ChDir "I:\PLANT\Process Control Room\Kiln Heat Up Archive"


With Workbooks("HH.16.014 Kiln Heat Up Form.xls")

Sheets("Chart1").Copy After:=Workbooks(2).Sheets(1)
End With
ActiveWorkbook.SaveAs Filename:=FileToSave, FileFormat:=xlNormal,
Password:="", writerespassword:="HEAT", _
ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks("HH.16.014 Kiln Heat Up Form.xls").Activate
Worksheets("HEAT UP").Delete
Worksheets(" Heat Up").Select

Range("B10:J44,A47:B47,E47:F47,I46:X47,L10:T44,V10:X44,V5:W5,C55,C66").
_
Select
Selection.ClearContents



End Sub

 

[Guest]

When you copy your chart, it is still referencing the orignal worksheet. You
can verify this by hand by copying the chart, and then right clicking on the
chart and viewing its DataSource. It should still be referencing the
original worksheet - not the copied worksheet.

To the best of my knowledge there really isn't any "easy" way to fix this.
The only thing I know to do, is to make a new (not copied) chart referencing
the newly copied worksheet.

So basically my suggestion would be to change your algorithm to:
1) copy worksheet
2) create new chart from copied worksheet
3) erase original worksheet for new data entry

This will require some more code on your part, but it isn't too bad, and
it's the only way I know of. Hopefully someone else has a better
suggestion...

John