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
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