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?