Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

that creates a new workbook but does not tell me that workbooks defsult name.
BUT you did give me an idea... once that new workbook is created I
immediately save it temp.xls. I can then reference that temp.xls file
whenever I want before saving it as the file name I want, and ultimately
deleting the temp.xls file.

So far so good... now if only i can figure out the kill statement ;)

"Jacob Skaria" wrote:

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

Kill full_file_name

--
__________________________________
HTH

Bob

"Stephen" wrote in message
...
that creates a new workbook but does not tell me that workbooks defsult
name.
BUT you did give me an idea... once that new workbook is created I
immediately save it temp.xls. I can then reference that temp.xls file
whenever I want before saving it as the file name I want, and ultimately
deleting the temp.xls file.

So far so good... now if only i can figure out the kill statement ;)

"Jacob Skaria" wrote:

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its
source to
the new workbook before renaming the new book with the appended date.
My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

You seem to be missing the beauty of the object model... you don't need to
know the name of the workbook. Using the code Jacob posted, once you set the
added workbook to the wb variable, you can reference that newly added
workbook through the wb variable without knowing the name Excel assigned to
it... just use wb wherever you would normally use Workbook("Book2") where I
have assumed the default name Excel assigned to the newly added workbook was
Book2. However, if you think you really need to specifically know its name,
just ask the wb variable...

NewWorkbookName = wb.Name

--
Rick (MVP - Excel)


"Stephen" wrote in message
...
that creates a new workbook but does not tell me that workbooks defsult
name.
BUT you did give me an idea... once that new workbook is created I
immediately save it temp.xls. I can then reference that temp.xls file
whenever I want before saving it as the file name I want, and ultimately
deleting the temp.xls file.

So far so good... now if only i can figure out the kill statement ;)

"Jacob Skaria" wrote:

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its
source to
the new workbook before renaming the new book with the appended date.
My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

By the way, I forgot to mention... if you vector through to the new workbook
using the wb variable like Jacob and I are suggesting, then there is no need
to save the workbook out to the hard disk and, consequently, no need to
"kill" it afterwards.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You seem to be missing the beauty of the object model... you don't need to
know the name of the workbook. Using the code Jacob posted, once you set
the added workbook to the wb variable, you can reference that newly added
workbook through the wb variable without knowing the name Excel assigned
to it... just use wb wherever you would normally use Workbook("Book2")
where I have assumed the default name Excel assigned to the newly added
workbook was Book2. However, if you think you really need to specifically
know its name, just ask the wb variable...

NewWorkbookName = wb.Name

--
Rick (MVP - Excel)


"Stephen" wrote in message
...
that creates a new workbook but does not tell me that workbooks defsult
name.
BUT you did give me an idea... once that new workbook is created I
immediately save it temp.xls. I can then reference that temp.xls file
whenever I want before saving it as the file name I want, and ultimately
deleting the temp.xls file.

So far so good... now if only i can figure out the kill statement ;)

"Jacob Skaria" wrote:

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its
source to
the new workbook before renaming the new book with the appended date.
My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

The new workbook will always be the last in the count; you do not need to
save and delete it;
Workbooks(workbooks.count).activate
Activeworkbook.name = "whatever"

"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

Stephen,

The .Name property will give you the name of a workbook. As Jacob noted,
adding a new workbook via the Set statement gives you the newly added
workbook as an object. From Jacobs code you can get the name via wb.Name.
If you have multiple workbooks open, those workbooks are given index numbers,
e.g. Workbooks(1), Workbooks(2), etc. You can refer to the indexed workbook
or the workbook name in the Workbooks statement. If you want to see each
workbook name, see the loop below.

As for the Kill statement, be careful when using this (i.e. make sure you
know what Kill is doing). Kill requires a fully qualified file name, and it
requires that the file not be open in order for Kill to execute properly.
So, if you have a Text.xls file, you can kill it by doing something like the
following:

Kill "C:\MyFolder\Test.xls"

Dim Wkb As Workbook
For Each Wkb In Workbooks
'Debug.Print prints to the Immediate Window (View | Immediate Window)
Debug.Print Wkb.Name
Next Wkb

Best,

Matthew Herbert

"Stephen" wrote:

that creates a new workbook but does not tell me that workbooks defsult name.
BUT you did give me an idea... once that new workbook is created I
immediately save it temp.xls. I can then reference that temp.xls file
whenever I want before saving it as the file name I want, and ultimately
deleting the temp.xls file.

So far so good... now if only i can figure out the kill statement ;)

"Jacob Skaria" wrote:

Try the below

Dim wb As Workbook
Set wb = Workbooks.Add
wb.Activate

If this post helps click Yes
---------------
Jacob Skaria


"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!

Reposting since msft failed to post previous answer, sorry if dupe

A new workbook is always the last one. You can find it with

Workbooks(workbooks.count).activate

to get the name

Workbooks.name = "whatever"

"Stephen" wrote:

Hi Folks,

I have a macro that opens a new workbook and copies info from its source to
the new workbook before renaming the new book with the appended date. My
problem is the new workbook is not alway Book1.

Anyone know a way to find the new workbook name and use that in my
Windows("New Workbook.xls").Activate statement?

TIA!