PDA

View Full Version : [SOLVED] Copy Method of Worksheet Class Failed



scott56
02-04-2009, 05:23 AM
Hi,

I have been trying to get a simple copy of a number of worksheets from one workbook to another and I keep striking the error "Copy Method of Worksheet Class Failed".....The source file contains only 8 worksheets, with text, some graphics and formatting, in addition there is some VBA script with some of the worksheets that are being copied.

The destination file is blank apart from a single worksheet that is in place before the copy begins.

Refer below to the code that I have been trying to get working...both the commented line that does just a single copy fails with the error as does the loop that I intend to use when I get past this error..

Any help would be appreciated...


Sub CopyMasterToTemplate()
Dim strTariffTemplateFileName As String
Dim strTariffTemplateFullFileName As String
Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Dim i As Integer
Dim srcWorkbook As Workbook
Dim dstWorkbook As Workbook
strTariffTemplateFileName = Worksheets("Master").Range("MasterTemplateFileName")
strTariffTemplateFullFileName = ActiveWorkbook.Path & "\" & strTariffTemplateFileName
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set srcWorkbook = ActiveWorkbook
Set dstWorkbook = objExcel.Workbooks.Open(strTariffTemplateFullFileName)
srcWorkbook.Activate
'srcWorkbook.Worksheets(1).Copy After:=dstWorkbook.Worksheets(1)
For i = 1 To srcWorkbook.Worksheets.Count
srcWorkbook.Worksheets(i).Copy After:=dstWorkbook.Worksheets(dstWorkbook.Worksheets.Count)
dstWorkbook.Worksheets(dstWorkbook.Worksheets.Count).Name = srcWorkbook.Worksheets(i).Name
Next i
dstWorkbook.SaveAs "New" & strTariffTemplateFullFileName
dstWorkbook.Close
objExcel.Quit
Set dstWorkbook = Nothing
Set objExcel = Nothing
End Sub

Bob Phillips
02-04-2009, 06:17 AM
Try replacing



Set srcWorkbook = ActiveWorkbook


with



Set srcWorkbook = objExcel.Workbooks(1)

scott56
02-04-2009, 12:34 PM
Hi,

Thanks for the suggestion....I tried that and got standard "Subscript out of Range" error...

The srcWorkbook as the Activework does provide valid references if I try and display a MsgBox with values from that workbook....so I think that reference is correct.

Could it be something else ?

GTO
02-04-2009, 01:39 PM
Greetings Scott,

I don't believe that there is a workbook in objExcel at that point, so that explains the subscript out of range...

Before mentioning what I think the problem is, I tried specifying ThisWorkbook instead - still no avail (copy fails).

Hopefully someone will quickly correct me if I are daffy on this point, but I don't believe you can copy/move worksheets from one Instance of an app to another. You can of course retrieve values as you mentioned.

In looking at the code you supplied, I am curious: is there a reason that you must use a secondary instance of the app? Why not just open the destination wb in the same instance as ThisWorkbook?

Mark

Bob Phillips
02-04-2009, 02:02 PM
Hi,

Thanks for the suggestion....I tried that and got standard "Subscript out of Range" error...

The srcWorkbook as the Activework does provide valid references if I try and display a MsgBox with values from that workbook....so I think that reference is correct.

Could it be something else ?

Yes I missed the fact that you hadn't opened any workbooks. What are you trying to do?

scott56
02-04-2009, 02:05 PM
Greetings Scott,

I don't believe that there is a workbook in objExcel at that point, so that explains the subscript out of range...

Before mentioning what I think the problem is, I tried specifying ThisWorkbook instead - still no avail (copy fails).

Hopefully someone will quickly correct me if I are daffy on this point, but I don't believe you can copy/move worksheets from one Instance of an app to another. You can of course retrieve values as you mentioned.

In looking at the code you supplied, I am curious: is there a reason that you must use a secondary instance of the app? Why not just open the destination wb in the same instance as ThisWorkbook?

Mark

Mark,

I don't know why I was opening another instance of Excel, when I removed that and replaced it with a basic open, the sheet copy worked.

So this one is solved....thanks for that