NigelTufnel
Board Regular
- Joined
- Apr 3, 2008
- Messages
- 53
- Office Version
- 365
- Platform
- Windows
I inherited a VBA project that (among other things) loops over Excel sheets and copies the lone chart in each sheet to a Word document.
The existing code was written by a VBA newbie. It works fine, but the programming makes it hard to modify/improve. For example, it copies the charts by first activating the Excel workbook, and then selecting the sheet, then selecting the chart and then copying the chart.
I've been revising the code and trying to improve it, but I seem to be having trouble revising this section of the code. I'm looking for some syntax to copy a chart from a workbook without selecting the workbook or the chart.
The prior code looks something like this. (This code is within a loop on the variable SheetNumber):
I tried to simplify this by assigning the Workbook an Excel object name (call it
This generates an "Object doesn't support this property or method" error.
Can someone help me with the practical and conceptual mistake I'm making here?
I have one additional question. One of the reasons I wanted to change this section of the code, is that I've run into a strange situation where the original code will break with an error. If I hit DEBUG and immediately hit F5 without making any changes whatsoever, the code will resume without error. Does anyone know why this happens?
Thanks for any help you can provide.
The existing code was written by a VBA newbie. It works fine, but the programming makes it hard to modify/improve. For example, it copies the charts by first activating the Excel workbook, and then selecting the sheet, then selecting the chart and then copying the chart.
I've been revising the code and trying to improve it, but I seem to be having trouble revising this section of the code. I'm looking for some syntax to copy a chart from a workbook without selecting the workbook or the chart.
The prior code looks something like this. (This code is within a loop on the variable SheetNumber):
VBA Code:
Workbooks(1).Activate
Sheets(SheetNumber).Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.copy
I tried to simplify this by assigning the Workbook an Excel object name (call it
wb
) and making one long referece (using WITH):
Code:
With wb.Sheets(SheetNumber)
(other code)
.ChartObjects(1).ChartArea.Copy
(other code)
End With
This generates an "Object doesn't support this property or method" error.
Can someone help me with the practical and conceptual mistake I'm making here?
I have one additional question. One of the reasons I wanted to change this section of the code, is that I've run into a strange situation where the original code will break with an error. If I hit DEBUG and immediately hit F5 without making any changes whatsoever, the code will resume without error. Does anyone know why this happens?
Thanks for any help you can provide.