Open Workbook to Specific Cell

[GEdwards]

I do not understand why Excel works this way, and would like help please to
understand it, and how I can work around it. I am using Excel 2003.

I have 2 workbooks. My test workbook (to understand how this is working)
has a macro behind a button that calls... Workbooks.Open
Filename:="E:\Address and Telephone Book.xls".

Now this works fine except that when the "Address and Telephone Book.xls" is
opened, the active cell happens to be the last active cell location from the
last time the "Address and Telephone Book.xls" was saved. This is very
annoying if I am in a workbook that has 1000+ rows and I attempt to open
another workbook frim it, and it opens at cell location F1003, but the newly
opened workbook has rows 1 thru 50 have actual data, then I have to scroll up
to where I want my active cell to be.

Can someone please explain why this happens and how I can get my second
workbook to open in a specific cell, such as A1 or S35?

 

[GEdwards]

Added info: Within my 2nd WB I have the following Sub upon opening, but it
does not appear to be totally working. It does lock all worksheets as
required but cell B3 does not become the active cell;

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="X9j10K32"

Range("B3").Activate
Range("B3").Select
End Sub

 

[Rick Rothstein]

What happens if you qualify the Range command (you only need one of those as
they both do the same thing) with Worksheets("Sheet1") like you did for the
Protect statement line?

Worksheets("Sheet1").Range("B3").Activate

 

[Gord Dibben]

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect Password:="X9j10K32"
.EnableSelection = xlNoRestrictions
End With
Range("B3").Select
End Sub


Gord Dibben MS Excel MVP

 

[Don Guillett]

try this in your workbook_open event

Application.Goto Sheets("sheet1").Range("s35"), scroll:=True

 

[GEdwards]

Rick and Gord,
Thanks for your feedback; my issue is now solved. I had the correct coding
as required however after debugging a larger macro that actually does the
OPEN, I later saw that I had a "Range(actvCell).select" (where actvCell was
defined as the active cell in the issuing macro). With that, it was of
course overriding my "Private Sub Workbook_Open()" routine.

Frustrated, but happy!