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 |
Yet another "copy method of worksheet class failed"
|
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? |
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? |
All times are GMT +1. The time now is 06:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com