Naming convention for a variable "Book1" while copying sheets

J

JER

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 Before:=Workbooks
("Book1").Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Before:=Workbooks("Book1").Sheets(1)
....
and on until the desired sheets (selected via a control)
are saved.



THANKS!
JER
 
B

Brad Vontur

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

Recommended changes

dim wbkNew as Workboo
set wbkNew = Workbooks.Ad
Windows("ps").Activat
Sheets("Environment").Selec
Sheets("Environment").Copy Before:=wbkNew.Sheets(1
Windows("ps").Activat
Sheets("CT").Selec
Sheets("CT").Copy Before:=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 Before:=wbkNew.Sheets(1
shtCT.Copy Before:=wbkNew.Sheets(1
Application.ScreenUpdating = True ' Refresh the screen

I hope that helps

-Brad
 
J

JER

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 Before:=wbkNew.Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Before:=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 Before:=wbkNew.Sheets(1)
shtCT.Copy Before:=wbkNew.Sheets(1)
Application.ScreenUpdating = True
' Refresh the screen.
End Sub
-----Original Message-----
When you execute the Add method of the Workbooks
Click to expand...
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 Before:=Workbooks ("Book1").Sheets(1)
Windows("ps").Activate
Sheets("CT").Select
Sheets("CT").Copy Before:=Workbooks("Book1").Sheets(1)

Recommended changes:

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

And consider this code to eliminate the whole "Select"
Click to expand...
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
Click to expand...
run faster, and your users won't see what's happening.
 
B

Brad Vontur

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
 
T

Tom Ogilvy

Set wbkPs = Workbooks("ps.xls")

Will always work.

Set wbkPs = Workbooks("ps")

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

JER

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






.
Click to expand...
 
J

JER

Thanks Brad.

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

JER
 
Top