Thankyou!

Have now got a handle on it!

JER
-----Original Message-----
Set wbkPs = Workbooks("ps.xls")

Will always work.

Set wbkPs = Workbooks("ps")

will only work if certain Windows level options (not
Excel) are chosen.

--
Regards,
Tom Ogilvy



"JER" wrote in
message
...
Thanks Brad, but I ran into some problems ---

entered the following as a test in a workbook called ps
with a sheet named environment and another named CT.

They generated debug messages as indicated:

Sub tryanswer()
Application.ScreenUpdating = False
Dim wbkNew As Workbook
Set wbkNew = Workbooks.Add
'Error 9 "Subscript out of Range" points to next
line
Windows("ps").Activate
Sheets("Environment").Select
Sheets("Environment").Copy Befo=wbkNew.Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Befo=wbkNew.Sheets(1)


'And consider this code to eliminate the whole "Select"
and "Activate" 'recorded macro programming.
End Sub

Sub tryanswer2()
Dim wbkPs As Workbook, wbkNew As Workbook
'Dim shtEnvironment As Sheet, shtCT As Sheet

Application.ScreenUpdating = False
' This will make it run faster, and your users won't see
what's happening.
'Error 9 "Subscript out of Range" points to next line
Set wbkPs = Workbooks("ps")
Set shtEnvironment = wbkPs.Sheets("Environment")
Set shtCT = wbkPs.Sheets("CT")

Set wbkNew = Workbooks.Add

shtEnvironment.Copy Befo=wbkNew.Sheets(1)
shtCT.Copy Befo=wbkNew.Sheets(1)
Application.ScreenUpdating = True
' Refresh the screen.
End Sub

-----Original Message-----
When you execute the Add method of the Workbooks
collection, set a variable to its return value. Then
you
can keep the workbook object in memory, and refer to it
that way.

Original code:

Workbooks.Add
Windows("ps").Activate
Sheets("Environment").Select
Sheets("Environment").Copy Befo=Workbooks
("Book1").Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Befo=Workbooks("Book1").Sheets
(1)

Recommended changes:

dim wbkNew as Workbook
set wbkNew = Workbooks.Add
Windows("ps").Activate
Sheets("Environment").Select
Sheets("Environment").Copy Befo=wbkNew.Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Befo=wbkNew.Sheets(1)

And consider this code to eliminate the whole "Select"
and "Activate" recorded macro programming.

dim wbkPs as Workbook, wbkNew as Workbook
dim shtEnvironment as Sheet, shtCT as Sheet

Application.ScreenUpdating = False ' This will make it
run faster, and your users won't see what's happening.
set wbkPs = Workbooks("ps")
set shtEnvironment = wbkPs.Sheets("Environment")
set shtCT = wbkPs.Sheets("CT")

set wbkNew = Workbooks.Add

shtEnvironment.Copy Befo=wbkNew.Sheets(1)
shtCT.Copy Befo=wbkNew.Sheets(1)
Application.ScreenUpdating = True ' Refresh the screen.

I hope that helps.

-Brad
.



.