"method 'Copy' of object '_Worksheet' failed"

I have a workbook that contains two sheets., "CostCentres" & "Template"
I need to open the workbook, look at a list of cost centres in the
"CostCentres" sheet, and for each one I need to copy the Template sheet.
So, if My list of Cost Centres contains 10 records I need to end up with
the workbook containing 10 copies of the Template sheet.

I have tried a couple of syntax's to achive this but each time I come up
with the "method 'Copy' of object '_Worksheet' failed" error each time. The
line that errors is

objExcelWB.Sheets("Template").Copy ,
objExcelWB.Sheets(objExcelWB.Sheets.Count)

Worksheet is not protected in any way and workbook is not ReadOnly.

Any ideas?

tia

Terry Holland

Here's a cut from a "timecard" sheet of mine that uses the sheet "Current"
as the current pay period, then makes a copy and clears it to become the
"Current" sheet for the next pay period.

Sheets("Current").Select
Sheets("Current").Copy After:=Sheets(1)

"Terry Holland" <[email protected]> wrote in message
news:OcXT%[email protected]...
>I have a workbook that contains two sheets., "CostCentres" & "Template"
> I need to open the workbook, look at a list of cost centres in the
> "CostCentres" sheet, and for each one I need to copy the Template sheet.
> So, if My list of Cost Centres contains 10 records I need to end up with
> the workbook containing 10 copies of the Template sheet.
>
> I have tried a couple of syntax's to achive this but each time I come up
> with the "method 'Copy' of object '_Worksheet' failed" error each time.
> The
> line that errors is
>
> objExcelWB.Sheets("Template").Copy ,
> objExcelWB.Sheets(objExcelWB.Sheets.Count)
>
> Worksheet is not protected in any way and workbook is not ReadOnly.
>
> Any ideas?
>
> tia
>
> Terry Holland
>
>

Hi Terry,

Assuming your cost centres are listed in column A of the CostCentres sheet ,
starting at A2 (to allow for a header), try:

Sub Tester4()
Dim objExcelWB As Workbook
Dim sh As Worksheet, sh1 As Worksheet
Dim i As Long, j As Long

Set objExcelWB = ActiveWorkbook
Set sh = objExcelWB.Sheets("Template")
Set sh1 = objExcelWB.Sheets("CostCentres")

Application.ScreenUpdating = False
'If there is no header row, delete the -1 in next line
i = sh1.Cells(Rows.Count, "A").End(xlUp).Row - 1

For j = 1 To i
With objExcelWB
sh.Copy after:=.Sheets(.Sheets.Count)
End With
Next j
Application.ScreenUpdating = True
End Sub


---
Regards,
Norman



"Terry Holland" <[email protected]> wrote in message
news:OcXT%[email protected]...
>I have a workbook that contains two sheets., "CostCentres" & "Template"
> I need to open the workbook, look at a list of cost centres in the
> "CostCentres" sheet, and for each one I need to copy the Template sheet.
> So, if My list of Cost Centres contains 10 records I need to end up with
> the workbook containing 10 copies of the Template sheet.
>
> I have tried a couple of syntax's to achive this but each time I come up
> with the "method 'Copy' of object '_Worksheet' failed" error each time.
> The
> line that errors is
>
> objExcelWB.Sheets("Template").Copy ,
> objExcelWB.Sheets(objExcelWB.Sheets.Count)
>
> Worksheet is not protected in any way and workbook is not ReadOnly.
>
> Any ideas?
>
> tia
>
> Terry Holland
>
>

Thanks

My code worked ok as soon as I put the line
Application.ScreenUpdating = False
into my code.


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Terry,
>
> Assuming your cost centres are listed in column A of the CostCentres sheet
,
> starting at A2 (to allow for a header), try:
>
> Sub Tester4()
> Dim objExcelWB As Workbook
> Dim sh As Worksheet, sh1 As Worksheet
> Dim i As Long, j As Long
>
> Set objExcelWB = ActiveWorkbook
> Set sh = objExcelWB.Sheets("Template")
> Set sh1 = objExcelWB.Sheets("CostCentres")
>
> Application.ScreenUpdating = False
> 'If there is no header row, delete the -1 in next line
> i = sh1.Cells(Rows.Count, "A").End(xlUp).Row - 1
>
> For j = 1 To i
> With objExcelWB
> sh.Copy after:=.Sheets(.Sheets.Count)
> End With
> Next j
> Application.ScreenUpdating = True
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "Terry Holland" <[email protected]> wrote in message
> news:OcXT%[email protected]...
> >I have a workbook that contains two sheets., "CostCentres" & "Template"
> > I need to open the workbook, look at a list of cost centres in the
> > "CostCentres" sheet, and for each one I need to copy the Template sheet.
> > So, if My list of Cost Centres contains 10 records I need to end up
with
> > the workbook containing 10 copies of the Template sheet.
> >
> > I have tried a couple of syntax's to achive this but each time I come up
> > with the "method 'Copy' of object '_Worksheet' failed" error each time.
> > The
> > line that errors is
> >
> > objExcelWB.Sheets("Template").Copy ,
> > objExcelWB.Sheets(objExcelWB.Sheets.Count)
> >
> > Worksheet is not protected in any way and workbook is not ReadOnly.
> >
> > Any ideas?
> >
> > tia
> >
> > Terry Holland
> >
> >
>
>