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

[Terry Holland]

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

 

[Norman Jones]

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

 

[Kurt]

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]

Thanks

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