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

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