Eric58132
05-04-2009, 09:16 AM
Hello All,
I've built a tool which creates charts, and with the push of a button I'd like to have them exported to Word. The code I've currently written is as follows:
Option Explicit
Sub PasteToWord()
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
' Change the range to suit your needs. See the How to Use for this code
Sheets("Printable Graphs").ChartObjects("3").Copy
AppWord.Documents.Add
AppWord.Selection.Paste
Application.CutCopyMode = False
Set AppWord = Nothing
End Sub
When executed, the graph DOES get exported to Word as planned, but I get a Run-Time error (1004) stating that "Select method of WOrksheet class failed". When I hit Debug, the error highlighted is in my Workbook_Open code, specifically "Sheets("Report Filter").Select (which is a completely different sheet.) The code in Workbook_Open is designed to unprotect the sheets of the workbook, update a pivot table, and then reprotect. I will note that the pivot table and the charts lie on different sheets.
Any ideas on how I can fix this issue? Thank you very much in advance.
I've built a tool which creates charts, and with the push of a button I'd like to have them exported to Word. The code I've currently written is as follows:
Option Explicit
Sub PasteToWord()
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
' Change the range to suit your needs. See the How to Use for this code
Sheets("Printable Graphs").ChartObjects("3").Copy
AppWord.Documents.Add
AppWord.Selection.Paste
Application.CutCopyMode = False
Set AppWord = Nothing
End Sub
When executed, the graph DOES get exported to Word as planned, but I get a Run-Time error (1004) stating that "Select method of WOrksheet class failed". When I hit Debug, the error highlighted is in my Workbook_Open code, specifically "Sheets("Report Filter").Select (which is a completely different sheet.) The code in Workbook_Open is designed to unprotect the sheets of the workbook, update a pivot table, and then reprotect. I will note that the pivot table and the charts lie on different sheets.
Any ideas on how I can fix this issue? Thank you very much in advance.