Macro - making another window active

  • Thread starter Thread starter jdtivoli
  • Start date Start date
J

jdtivoli

I have created a macro which opens a second spreadsheet.
However, after it opens the second spreadsheet, it reverts back to th
first spreadsheet as the active one.

What I would really like it to do is to hide the first spreadsheet an
thus make the second one the active spreadsheet.

How
 
But it's not another workbook in the same spreadsheet. It's anothe
spreadsheet which it opens and I want to be the active one
 
What do you mean then by open ?

Worksheets("Sheet2").Activate will make it the current sheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi jdtivoli, I think we may be getting our terms mixed up. A "worksheet
is housed within a "workbook". A workbook can be named something lik
"Book1.xls". The default name of the worksheets in a workbook, will b
"Sheet1", "Sheet2", and "Sheet3" (most people have 3 sheets the defaul
setting for new sheets in a workbook). Now, if you mean you have
workbook open, say "Book1.xls", and you run code that opens anothe
workbook, say "Book2.xls" and it reverts back to "Book1.xls" as th
active workbook but you would like "Book2.xls" to be the activ
workbook, then the line:

ActiveWindow.ActivateNext

is one way to do this. Another would be:

Workbooks("Book2.xls").Activate

If you meant that in your one workbook you have "Sheet1" activated, bu
would like to have "Sheet2" activated instead, you can use:

Worksheets("Sheet2").Activate

Hope this hasn't confused the issue...

Good luck,

Dave M
 
I tried all of your suggestions. None worked.

I think the problem is that I am trying to add the
Workbooks("Book2.xls").Activate statement to an existing macro.

The current macro reads:

Sub Macro2()

Workbooks.Open Filename:="C:\BBTDATA\Book2.xls"

End Sub


Where in the above statement should I add your line?

If I add it above the End Sub line then it just seems to ignore the ne
line.

If I add it below the End Sub line I get an error message saying tha
only comments may be added below the End Sub line.

If I add:
Sub Macro()
Workbooks("Book2.xls").Activate
End Sub

It just ignores this statement.

What would be the correct way to do this.

Also, if I create a separate macro containing only your statement, i
works fine. And if another macro is the answer - then how do I ge
this macro to run that macro after doing the first thing that thi
macro is created to do
 
You have this sub in Book1.xls?

Sub Macro2()
Workbooks.Open Filename:="C:\BBTDATA\Book2.xls"
End Sub

And book2.xls isn't the activeworkbook after you open it?

When I open a nice, normal workbook, it becomes the activeworkbook.

If I opened a workbook that was hidden, then it doesn't become the
activeworkbook.

Any chance that book2.xls was hidden (Via Windows|Hide)?

Option Explicit
Sub testme()

Dim wkbk2 As Workbook
Set wkbk2 = Workbooks.Open(Filename:="book3.xls")
wkbk2.Windows(1).Visible = True

wkbk2.Activate

End Sub

When I unhid the window, it became the active workbook/window. You may not need
the last line, but it doesn't hurt (much).
 
Hi again,

I would try Dave Peterson's suggestion above...I'm not sure why yo
would be having that problem....

To run a sub from another sub, you would just call the name of the su
you want to run...for instance, using your example from above an
assuming both Macro's are housed in Module1 and named Macro2 an
Macro3:

Sub Macro2()

Workbooks.Open Filename:="C:\BBTDATA\Book2.xls"
Macro3

End Sub

or, if it is housed in a different Module, put the Module name first
for instance, Module2.Macro3...

Hope this helps...let us know what happens....

Dave M
 
Back
Top