MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro to switch between spreadsheets

Posted by Ian Bartlett on April 28, 2001 2:06 AM

I have two spreadsheets open, call them Book1 and Book2. In Book1, I have a cell named [Worksheet_Name], which contains a formula with ‘Book2.xls’ as its result.

I'm trying to switch from Book1 to Book2 with the following code:

Windows [Worksheet_Name].Activate

ie. to switch to a particular spreadsheet based on the contents of a cell.
This code gives me an ‘Invalid use of property’ error. I’ve tried various combinations of parentheses and quotes, without success. Any ideas?

Many thanks,

Ian

Posted by Dave Hawley on April 28, 2001 3:11 AM


Hi Ian

Use:

Dim SBookName As String
SBookName = Range("Worksheet_Name")
Windows(SBookName).Activate


But the named cell "Worksheet_Name" must be in the ActiveWorkbook.

Dave


OzGrid Business Applications

Posted by Ian Bartlett on April 28, 2001 3:43 AM

Brilliant!

Thanks again,

Ian