Thread: 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).Copy
after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp.Sheets.Count)
vMoveSheets = vMoveSheets + 1
Loop
End If

PS: If any option would be better then I would entertain that as well.
pdr

pdr3@hotmail.com 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).Copy
> after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp.Sheets.Count)
> vMoveSheets = vMoveSheets + 1
> Loop
> End If

"pdr3@hotmail.com" 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).Copy
> after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp.Sheets.Count)
> vMoveSheets = vMoveSheets + 1
> Loop
> End If
>
>

Which line does the code stop at?

The line it stops at is the copy line -
CurrentTemplateWorkbook.Worksheets(vSheet.Name).Copy
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:
> "pdr3@hotmail.com" 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).Copy
> > after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp.Sheets.Count)
> > vMoveSheets = vMoveSheets + 1
> > Loop
> > End If
> >
> >
>
> Which line does the code stop at?

I have solved the issue of the range copy not working. What I needed
to do was perform it at the worksheet level but not as I understood it.
As oppose to doing it WorkBook.WorkSheet(index).Range().copy, I needed
to define a worksheet variable as follows:
Dim wstWorkSheet as WorkSheet
wstWorkSheet.Range("A:Z").Copy

I still have not been able to do a direct copy of worksheets between
two external workbooks.
Ex:
Open workbook1 (controling workbook for management) and identify
workbooks to be manipulated/tracked. Once identified merge all
identified workbooks into a master workbook for reporting.
Workbook1 creates workbook2 for merging. Next workbook1 opens the
identified workbooks and copies the data worksheets (typically
worksheet 3 - specially named) from the identified worksheets to
workbook2 (merging workbook). Worksheets are tracked to ensure no
duplicates.

All aid is appreciated.

Thanks,
pdr

pdr3@hotmail.com wrote:
> The line it stops at is the copy line -
> CurrentTemplateWorkbook.Worksheets(vSheet.Name).Copy
> 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:
> > "pdr3@hotmail.com" 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).Copy
> > > after:=Test_ConfigTemp.Worksheets(Test_ConfigTemp.Sheets.Count)
> > > vMoveSheets = vMoveSheets + 1
> > > Loop
> > > End If
> > >
> > >
> >
> > Which line does the code stop at?