Copy sheet into a new workbook

[Mike]

Hi,

I would like to copy a sample sheet from one workbook into a new workbook.
This should happen repeatidly (within for ... next statement), as the copied
sheet will be filled with data. So I wrote following macro:

Sub CopySheet()
Dim NewWb As Workbook
Dim NewWs As Worksheet

Set NewWb = Workbooks.Add

For i = 1 To 5
SheetTemplate. Copy NewWb
Set NewWs = ActiveSheet

'...Code
Next i
End Sub

.... which doesn't work. I get the 1004 run-time error (copy failed).

Can anybody help me to fix it?

Thank you!

Mike

 

[Tom Ogilvy]

Sub CopySheet()
Dim NewWb As Workbook
Dim NewWs As Worksheet

Set NewWb = Workbooks.Add

For i = 1 To 5
with NewWb
SheetTemplate.Copy After:=.Worksheets(.Worksheets.count)
end with
Set NewWs = ActiveSheet

'...Code
Next i
End Sub

SheetTemplate is troubling. I don't know what it is, where it is, or how it
is defined, so if you have a problem it probably will be with that.

 

[Mike]

Thank you Tom!

I already found the problem. I didn't use the destination in the copy
statement (After:=), so it must be exactly as you suggested.

SheetTemplate is the CodeName of the sheet that is to be copied, I prefer to
use it rather than reference throug the Sheets() collection.

Regards,

Mike