Excel macro to copy and paste a chart and a table to a new workbook

I have an Excel 2010 file that contains many worksheets. On each worksheet, I have a chart and and several tables resulting from a series of calculations, I would like to create a macro that will copy the chart and two of the summary tables to a new workbook. I tried to record a macro. but each time it gives an error about the book name. Thank you for your help!
Sub Macro1()
' Macro1 Macro
'
Range("B41:h128").Select
Selection.Copy
Workbooks.Add
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Windows("system.xlsm").Activate
ActiveSheet.ChartObjects("Chart 7").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
Windows("Book5").Activate Range("j6").Select
ActiveSheet.Paste

Hi there,

Try the following code and see if it works for you:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M