activating workbooks

[Luis939]

by any chance lets say i have only 2 workbooks open, if i'm working in one, is there any command or anything that says select the other workbook open, so that i can just switch between them, thanks!

 

[RobBroekhuis]

The best way to deal with this is to have a workbook object variable for each of the two workbooks, for example:

dim wb1 as workbook, wb2 as workbook
set wb1=activeworkbook
workbooks.add
set wb2=activeworkbook
wb1.activate
'do stuff in the original workbook
wb2.activate
'do stuff in the new workbook

You can certainly say

workbooks(1).activate and workbooks(2).activate, but this depends on there being just two workbooks open, and isn't particularly robust.
Rob

 

[lambuhere1]

I prefer using Ctrl and Tab key combination to shuffle between various open workbooks. Isnt it great!!!

Thanks

Ram P

 

[RobBroekhuis]

Since this is the VBA forum, I assumed 4335 was looking for a VBA answer. I certainly use ^tab when I'm manually shifting through Excel books.
Rob

 

[Luis939]

thanks Rob!

 

[Luis939]

well how about between worksheets in a workbook, how would i say select the next worksheet?

 

[Luis939]

just to add to my previous post, how do i first say go to the first worksheet in the workbook, then select the next worksheet, thanks!

 

[RobBroekhuis]

The following sub will select the next sheet, and go back to the first after it hits the last sheet. You'll have to do additional testing if you have hidden sheets.

sub ActivateNextSheet
if activesheet.index=activeworkbook.sheets.count then
sheets(1).activate
else
sheets(activesheet.index+1).activate
end if
end sub
Rob

 

[Luis939]

oh ok thanks alot rob, you always come through

 

[lambuhere1]

You can use Ctrl in combination with PageUp and PageDpwn to move between various worksheets of a workbook.

Rob is right in using his VBA.

Ram P