"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

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

Thanks

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


"Norman Jones" wrote in message
...
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" wrote in message
...
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