Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yet another "copy method of worksheet class failed"
I am creating a macro to copy worksheets from an external workbook to
the current active workbook. I am receiving the error mentioned in the subject. The code snippet is here. Shown is the active workbook and another workbook (templates are standard workbooks). If the sheet count is greater than three (filled sheet of data) the I want to copy in the full sheets that have that data. The first two sheets are notes sheets so they are not of value in this spreadsheet. I am able to communicate with both sheets but unable to perform the copy. The copy fails on the FIRST pass - always. All help is appreciated. Thanks, pdr Dim vSheet As Worksheet Dim CurrentTemplateWorkbook As Workbook Dim Test_ConfigTemp As Workbook Set Test_ConfigTemp = Excel.ActiveWorkbook Set xlApp = CreateObject("Excel.Application") Set CurrentTemplateWorkbook = xlApp.Workbooks.Open(ActiveWorkbook.Path & "\Templates\" & pWorkBookName & ".xls") If CurrentTemplateWorkbook.Sheets.Count 3 Then Do While vMoveSheets <= CurrentTemplateWorkbook.Sheets.Count - 3 Set vSheet = CurrentTemplateWorkbook.Worksheets(vMoveSheets + 2) MsgBox (vSheet.Name) 'check sheet name CurrentTemplateWorkbook.Worksheets(vSheet.Name).Co py after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp. Sheets.Count) vMoveSheets = vMoveSheets + 1 Loop End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yet another "copy method of worksheet class failed"
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yet another "copy method of worksheet class failed"
" wrote: I am creating a macro to copy worksheets from an external workbook to the current active workbook. I am receiving the error mentioned in the subject. The code snippet is here. Shown is the active workbook and another workbook (templates are standard workbooks). If the sheet count is greater than three (filled sheet of data) the I want to copy in the full sheets that have that data. The first two sheets are notes sheets so they are not of value in this spreadsheet. I am able to communicate with both sheets but unable to perform the copy. The copy fails on the FIRST pass - always. All help is appreciated. Thanks, pdr Dim vSheet As Worksheet Dim CurrentTemplateWorkbook As Workbook Dim Test_ConfigTemp As Workbook Set Test_ConfigTemp = Excel.ActiveWorkbook Set xlApp = CreateObject("Excel.Application") Set CurrentTemplateWorkbook = xlApp.Workbooks.Open(ActiveWorkbook.Path & "\Templates\" & pWorkBookName & ".xls") If CurrentTemplateWorkbook.Sheets.Count 3 Then Do While vMoveSheets <= CurrentTemplateWorkbook.Sheets.Count - 3 Set vSheet = CurrentTemplateWorkbook.Worksheets(vMoveSheets + 2) MsgBox (vSheet.Name) 'check sheet name CurrentTemplateWorkbook.Worksheets(vSheet.Name).Co py after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp. Sheets.Count) vMoveSheets = vMoveSheets + 1 Loop End If Which line does the code stop at? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yet another "copy method of worksheet class failed"
The line it stops at is the copy line -
CurrentTemplateWorkbook.Worksheets(vSheet.Name).Co py after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp. Sheets.Count) with the error. I have tried to perform a range copy with similar results. For some reason I cannot get the copy to copy the sheet either into the current workbook or ideally into a separate external workbook. My goal is to take worksheets from multiple workbooks and compile them into a single workbook for review and documentation. pdr Matt Lunn wrote: " wrote: I am creating a macro to copy worksheets from an external workbook to the current active workbook. I am receiving the error mentioned in the subject. The code snippet is here. Shown is the active workbook and another workbook (templates are standard workbooks). If the sheet count is greater than three (filled sheet of data) the I want to copy in the full sheets that have that data. The first two sheets are notes sheets so they are not of value in this spreadsheet. I am able to communicate with both sheets but unable to perform the copy. The copy fails on the FIRST pass - always. All help is appreciated. Thanks, pdr Dim vSheet As Worksheet Dim CurrentTemplateWorkbook As Workbook Dim Test_ConfigTemp As Workbook Set Test_ConfigTemp = Excel.ActiveWorkbook Set xlApp = CreateObject("Excel.Application") Set CurrentTemplateWorkbook = xlApp.Workbooks.Open(ActiveWorkbook.Path & "\Templates\" & pWorkBookName & ".xls") If CurrentTemplateWorkbook.Sheets.Count 3 Then Do While vMoveSheets <= CurrentTemplateWorkbook.Sheets.Count - 3 Set vSheet = CurrentTemplateWorkbook.Worksheets(vMoveSheets + 2) MsgBox (vSheet.Name) 'check sheet name CurrentTemplateWorkbook.Worksheets(vSheet.Name).Co py after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp. Sheets.Count) vMoveSheets = vMoveSheets + 1 Loop End If Which line does the code stop at? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Move Method of Worksheet Class failed" | Excel Programming | |||
XL2003 "paste method of worksheet class failed" | Excel Programming | |||
Error Message "Select method of worksheet class failed" | Excel Programming | |||
"Copy Method of worksheet class failed" | Excel Programming | |||
VBA error 1004 "Select method of Range class failed" | Excel Programming |