Excel Macro requires me to restart my computer to copy and paste graphs to a new sheet

[nomoreerrorsplease]

Yesterday I recorded a macro that copies data and three graphs from one worksheet and pastes them onto a new sheet. The first time I ran the macro, it copied and pasted the data and graphs perfectly. The second time (and every time after that), it pasted the data correctly, and then the code broke when it tried to copy a graph. I tried closing and re-opening Excel, and running the macro on a duplicated workbook, but I still kept getting the error: "Run-time error '-2147221040 (800401d0)': Method 'Copy' of object 'ChartArea failed." I restarted my computer and then re-ran the macro, and again, it only worked correctly the first time and then ran into the same error. And the error occurs randomly on any one of the three graphs with each run. I showed it to my co-worker, and she thinks there may be some kind of duplication going on in the background that causes the copy to fail until I restart my computer. I'm very new to macros and have no idea what other commands I could try, or what kind of troubleshooting could remedy this. I attached the code below, and would appreciate any help.

Sub Macro12()
'
' Macro12 Macro
'

'Generate new report
Sheets.Add After:=ActiveSheet
ActiveSheet.Select
ActiveSheet.Name = "RENAME AS TODAY'S DATE"

'Add Flow and Belt Press Data
ActiveWindow.SmallScroll Down:=3
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-12
Range("A1:B36").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RENAME AS TODAY'S DATE").Select
ActiveWindow.SmallScroll Down:=-18
Range("H1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=18
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=21
Range("A39:D53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RENAME AS TODAY'S DATE").Select
Range("F41").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste

'Add Flow Graph
ActiveWindow.SmallScroll Down:=3
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=6
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy <---------------------------- Code Breaks Here
Sheets("RENAME AS TODAY'S DATE").Select
Range("A41").Select
ActiveSheet.Paste

'Add BP Graph 1
ActiveWindow.SmallScroll Down:=6
Sheets("Data").Select
ActiveSheet.ChartObjects("Chart 2").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy <-----------------------------Or Here
Sheets("RENAME AS TODAY'S DATE").Select
Range("A56").Select
ActiveSheet.Paste

'Add BP Graph 2
Sheets("Data").Select
ActiveSheet.ChartObjects("Chart 3").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy <-----------------------------Or Here
Sheets("RENAME AS TODAY'S DATE").Select
Range("D56").Select
ActiveSheet.Paste
Range("D49").Select
End Sub

[ORoos]

Hi there,

It would be helpful if you attached a sample file (removing or anonymizing any sensitive/confidential data) showing what you have and what you need to do (see yellow banner at the top).
I did create a file with some dummy data and loaded your code.
It worked fine the first time. I did delete the new sheet the macro did create and run the code again. Worked fine.
I then did rename the created sheet and run the code again. No problem. I repeated this a few times, worked each time.


Recording macros is a great way to learn. The recorder however does sometimes record a lot of unnecessary lines.
Here is a slightly cleaner/shorter version of your code.

Please Login or Register  to view this content.