Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet into a new workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet into a new workbook
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. -- Regards, Tom Ogilvy "Mike" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet into a new workbook
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Mike" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet cells into differnt workbook/sheet, How? | Excel Discussion (Misc queries) | |||
Copy Sheet from One workbook to another EXISTING workbook | Excel Programming | |||
automatic copy and paste from sheet to sheet in a workbook | Excel Programming | |||
Copy sheet from one workbook to another workbook problem | Excel Programming |