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/