You need to use a variable for this.

Example:

Sub CreateNewWorkbook()

Dim wkbNew As Workbook

'Add the new workbook and store it in a variable
Set wkbNew = Application.Workbooks.Add

'Do some stuff to the new workbook here

'Activate it when you need it
wkbNew.Activate

End Sub

If you're opening a saved workbook, the concept is the same but the method
used to retrieve the workbook is different.

Ex:

Sub OpenWorkbook()

Dim wkbNew As Workbook

'Add the new workbook and store it in a variable
Set wkbNew = Application.Workbooks.Open("C:\Documents and
Settings\Rob\My Documents\TEST.xls")

'Do some stuff to the new workbook here

'Activate it when you need it
wkbNew.Activate

End Sub


FYI: If you have a reference to a workbook stored in a variable, you do not
have to activate it in order to manipulate it (in most cases). None of the
following code accesses the workbook its changing.

Ex:

wkbNew.Worksheets(1).Name = "TEST"
wkbNew.Worksheets(1).Range("A1:A5").Font.Bold = True


--
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP-Excel


"cornishbloke" wrote in message
...
Hopefully this is a simple question that I can't find the answer for
elsewhere...

in VBA, how do I activate the most recent new worksheet?

I have a macro which opens a new workbook and then later needs to
activate this window. Having recorded this using the macro recorder
the code is static (e.g. refers to "book1") and won't work again unless
I manually change these values to the next new workbook name.

Thanks in Advance.


---
Message posted from http://www.ExcelForum.com/