Naming convention for a variable "Book1" while copying sheets
 

I have gotten this far in developing a macro for saving
sheets to a new workbook that users will name themselves.
I can not count on the new workbook to be "Book1" all the
time as the user may have one or more new Books (Book1-n)
open and the next book number would be book1-n+1.

So I need to somehow establish a convention to assign a
name to the new book in the following code (in earlier
versions of this saga I was copying only the input cells
to a new book and the solution there was to simply
use "Windows(2)" as the second book. That doesn't work in
this case.)


Sub sheetsavetrialz()
'
'This starts the save process which saves all cells with
required or optional user input in the selected areas to a
new workbook.
'establishes "ps" as the name for the active workbook
ps = ThisWorkbook.Name
Windows(ps).Activate
ActiveWindow.WindowState = xlMinimized

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)
....
and on until the desired sheets (selected via a control)
are saved.



THANKS!
JER

Naming convention for a variable "Book1" while copying sheets
 

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.Ad
Windows("ps").Activat
Sheets("Environment").Selec
Sheets("Environment").Copy Befo=Workbooks("Book1").Sheets(1
Windows("ps").Activat
Sheets("CT").Selec
Sheets("CT").Copy Befo=Workbooks("Book1").Sheets(1

Recommended changes

dim wbkNew as Workboo
set wbkNew = Workbooks.Ad
Windows("ps").Activat
Sheets("Environment").Selec
Sheets("Environment").Copy Befo=wbkNew.Sheets(1
Windows("ps").Activat
Sheets("CT").Selec
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 Workboo
dim shtEnvironment as Sheet, shtCT as Shee

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.Ad

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

I hope that helps

-Brad

Naming convention for a variable "Book1" while copying sheets
 

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
.

Naming convention for a variable "Book1" while copying sheets
 

I left out the line that you had in your original code..

ps = ThisWorkbook.Nam

The out of range errors are coming from the missing "ps" variable

-Brad

Naming convention for a variable "Book1" while copying sheets
 

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
.

Naming convention for a variable "Book1" while copying sheets
 

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
.



.

Naming convention for a variable "Book1" while copying sheets
 

Thanks Brad.

Between you and Tom Olgivy I am now on the functioning
track to what I need done!

JER

-----Original Message-----
I left out the line that you had in your original code...

ps = ThisWorkbook.Name

The out of range errors are coming from the missing "ps"
variable.

-Brad
.