Activate new window

  • Thread starter Thread starter cornishbloke
  • Start date Start date
C

cornishbloke

Hopefully this is a simple question that I can't find the answer fo
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 t
activate this window. Having recorded this using the macro recorde
the code is static (e.g. refers to "book1") and won't work again unles
I manually change these values to the next new workbook name.

Thanks in Advance
 
Set a reference to the workbook when you create it. In a general Module.

Public NewBook as Workbook

Public sub OpenBook()
Set NewBook = Workbooks.Add
End Sub

Public Sub ActivateBook()
NewBook.Activate
End Sub
 
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
 
Back
Top