Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy every worksheet
I need to copy each worksheet from the active workbook into a new workbook.
I've been fighting with the following code and am hoping can point out my mistake. Set DataWrkBk = Workbooks(ActiveWorkbook.Name) Workbooks.Add Sheets("Sheet1").Select Set NewWrkBk = ActiveWorkbook NewWrkBkName = ActiveWorkbook.Name Windows("Weekly_Stats.xls").Activate For Each ws In DataWrkBk.Worksheets wSheet.Copy After:=NewWrkBk.Worksheets(NewWrkBk.Worksheets.Cou nt) Debug.Print ws.Name Next ws It does the first sheet fine and then spits out an error:424 object required and highlights the wSheet.Copy line? Why would it work once and then stop? Thank you for the helping hand. QB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy every worksheet
You could try:
datawrkbk.sheets.copy or datawkrbk.worksheets.copy (don't create the "to" workbook first, either. The .copy will do that for you.) ps. Set DataWrkBk = Workbooks(ActiveWorkbook.Name) is the same thing as: Set DataWrkBk = ActiveWorkbook QB wrote: I need to copy each worksheet from the active workbook into a new workbook. I've been fighting with the following code and am hoping can point out my mistake. Set DataWrkBk = Workbooks(ActiveWorkbook.Name) Workbooks.Add Sheets("Sheet1").Select Set NewWrkBk = ActiveWorkbook NewWrkBkName = ActiveWorkbook.Name Windows("Weekly_Stats.xls").Activate For Each ws In DataWrkBk.Worksheets wSheet.Copy After:=NewWrkBk.Worksheets(NewWrkBk.Worksheets.Cou nt) Debug.Print ws.Name Next ws It does the first sheet fine and then spits out an error:424 object required and highlights the wSheet.Copy line? Why would it work once and then stop? Thank you for the helping hand. QB -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy every worksheet
If you are attempting to make a copy of the active workbook have you thought
about using SaveCopyAs? from the help file: Example This example saves a copy of the active workbook. ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS" -- jb "QB" wrote: I need to copy each worksheet from the active workbook into a new workbook. I've been fighting with the following code and am hoping can point out my mistake. Set DataWrkBk = Workbooks(ActiveWorkbook.Name) Workbooks.Add Sheets("Sheet1").Select Set NewWrkBk = ActiveWorkbook NewWrkBkName = ActiveWorkbook.Name Windows("Weekly_Stats.xls").Activate For Each ws In DataWrkBk.Worksheets wSheet.Copy After:=NewWrkBk.Worksheets(NewWrkBk.Worksheets.Cou nt) Debug.Print ws.Name Next ws It does the first sheet fine and then spits out an error:424 object required and highlights the wSheet.Copy line? Why would it work once and then stop? Thank you for the helping hand. QB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy every worksheet
QB,
The sub below will copy the sheets in the active workbook to a new workbook and activate the new workbook. Values, formatting and any VBA code attached to the specific sheets will be retained in the new book, VBA code attached to the Workbook or in any modules will be left behind. '----------------------------------------- Sub CopyToNewBook() Dim s As Integer Dim b As Integer Dim vArray As Variant s = Application.Worksheets.Count b = Application.Workbooks.Count ReDim vArray(s - 1) For x = 0 To s - 1 vArray(x) = Worksheets(x + 1).Name Next x Worksheets(vArray).Copy Set objNewBook = Application.Workbooks(b + 1) objNewBook.Activate End Sub '----------------------------------------- Steve Yandl "QB" wrote in message ... I need to copy each worksheet from the active workbook into a new workbook. I've been fighting with the following code and am hoping can point out my mistake. Set DataWrkBk = Workbooks(ActiveWorkbook.Name) Workbooks.Add Sheets("Sheet1").Select Set NewWrkBk = ActiveWorkbook NewWrkBkName = ActiveWorkbook.Name Windows("Weekly_Stats.xls").Activate For Each ws In DataWrkBk.Worksheets wSheet.Copy After:=NewWrkBk.Worksheets(NewWrkBk.Worksheets.Cou nt) Debug.Print ws.Name Next ws It does the first sheet fine and then spits out an error:424 object required and highlights the wSheet.Copy line? Why would it work once and then stop? Thank you for the helping hand. QB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy method of worksheet class failed: trying to copy a hidden she | Excel Programming | |||
copy method of worksheet class failed: trying to copy a hidden she | Excel Programming | |||
copy method of worksheet class failed: trying to copy a hidden she | Excel Programming | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming |