Macro to export chart to excel

[raghavsurya]

hi All,

I am trying to execute below mentioned macro and I am getting an error as Subscript out of range in the line highlighed in bold below. The excel is opening but the chart is not getting transferred to Excel.

Can somebody help me.

Sub Clickit
SheetIt "C:\Users\raghavendra.s\Desktop\test.xls","OldSheetname","TB01","NewSheetname"
End sub

function SheetIt(ExcelDoc,SheetName,ChartName,Category)
set XLS = createobject("Excel.Application")
XLS.Visible = True
Set Workbook = XLS.Workbooks.Open(ExcelDoc)
Set Worksheet = XLS.Worksheets(NewSheetname)
Worksheet.Activate
set obj = ActiveDocument.getsheetobject(TB01)
obj.CopyTableToClipboard true
Worksheet.paste
Worksheet.cells.select
Worksheet.cells.mergecells = false
Worksheet.Cells.EntireRow.RowHeight = 12.75
Worksheet.Cells.EntireColumn.AutoFit
Worksheet.name = Category
end function

Thanks and Regards,

Raghav

[#2]

Hi Raghav,

You get this error because the sheet doesn't exist.  You need to specify an existing sheet, usually "Sheet 1".

Are you trying to assign the sheetname dynamically?

Kind regards,

Andrew