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.Count)
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

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.Count)
> 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

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.Count)
> 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