Subscript out of range when copying charts from excel to word

1
votes

Hi i'm facing a subscript out of range error when I tried to copy a chart from excel sheet to word document. I checked the worksheet name it seems to be fine. I tried changing to ThisWorkbook.Sheets(wsname) but i received an application define type error. How do I go about resolving this issue.

With ThisWorkbook.Worksheets(wsName)
   .ChartObjects(1).Activate
   ActiveChart.ChartArea.Copy
End With

The name of the worksheet I'm trying to reference

enter image description here

1
vbaexcelexcel-charts
.ChartObjects(1).ChartArea.Copy doesn't work? – serakfalcon
as in the error is at with ThisWorkbook.worksheets(wsName) – Keenlearner
Subscript our of range means that sheet wsName does not exist in the workbook that runs VBA. You are using either a wrong name, or a wrong workbook (if you have multiple workbooks open). – RADO
hi it couldn't be the wrong workbook since I'm using thisworkbook, it couldn't be the worksheet name since wsName is inlet Filter, if it helps, the worksheet I'm trying to reference is a chart sheet – Keenlearner
using thisworkbook does not guarantee anything... Excel is no longer an MDI document manager, so you need to qualify your worksheets with objects – user8127890

1 Answers

2
votes

I have finally figured out what's the problem.

Since I'm referencing a chartsheet and not a worksheet, I have to use .Charts instead of .Worksheets. In addition since it is a chartsheet, the chartsheet itself is a chart object, hence .ChartObjects(1) is not required, only .ChartArea.Copy

Correct Code:

With ThisWorkbook.Charts(wsName)
  .ChartArea.Copy
End With