Opening workbook at sheet required.

[DB.]

I've a 3-sheet workbook that always opens at Sheet 2 - I want it to open
at Sheet 1.
I know that the standard answer here is "The workbook opens at the sheet
on screen when the file was last saved" but in this case it doesn't! How
can I get it to work in the proper way, please?

Another problem with this Sheet 2 (but not Sheets 1 & 3).
I've only about 34 rows in use but the sheet offers me 65536 rows
(available by scrolling) instead of the 70-or so rows that I would expect.
How do I correct this please?

TIA of any reply,
DB.

 

[Don Guillett]

Then look in the ThisWorkbook module for an autoopen or workbook open event
that takes you to sheet 2.

 

[MartinW]

Hi DB,

Could be all sorts of reasons for this most of which I couldn't even
begin to guess, but probably the easiest thing to do is open a new
sheet, copy and paste your data to the new sheet and delete
the old sheet. Use your mouse to select your copy data and not "ctrl a"

HTH
Martin

 

[DB.]

Hi DB,

Could be all sorts of reasons for this most of which I couldn't even
begin to guess, but probably the easiest thing to do is open a new
sheet, copy and paste your data to the new sheet and delete
the old sheet. Use your mouse to select your copy data and not "ctrl a"

HTH
Martin

Thanks, Martin. I'd already tried your solution before you suggested
it. But that copies the cell numerical values into the new sheet without
transferring the formulae that created them. (The formula bar contains the
number in the cell, not the formula that led to it).
Perhaps I'll have to live with my original (minor) problem!

BW's,
DB.

 

[MartinW]

Hi DB,

But that copies the cell numerical values into the new sheet without

transferring the formulae that created them.<

Err No. That pastes the formulae or if there is no formula in a cell it
pastes the value

(The formula bar contains the number in the cell, not the formula that led
to it).<

Errr No again. The formula bar displays the true value of a cell whether it
is a formula or a number or text or whatever and that is the value that is
copied, not the 'display' that is shown in the actual cell.

Regards
Martin

 

[DB.]

Hi DB,

transferring the formulae that created them.<

Err No. That pastes the formulae or if there is no formula in a cell it
pastes the value


Errr No again. The formula bar displays the true value of a cell whether
it
is a formula or a number or text or whatever and that is the value that is
copied, not the 'display' that is shown in the actual cell.

Regards
Martin

Thanks for coming back again, Martin. I've an apology to make...

I was opening a new workbook and copying the data there - not, as it seems
you were suggesting, opening a new sheet in the same workbook. And, yes (I
insist - I've spent all morning p*ss*ng about with this!) doing that *does*
copy across numbers, not formulae.

When I opened a new sheet (sheet 3) in the *same* workbook and copied the
data across to it (from sheet 2) formulae went over to it satisfactorily.
Not now needing sheet 2 I tabbed it up and tried to delete it (Edit - Delete
Sheet) but 'Delete Sheet' was greyed-out - indeed, it's greyed out IRO tabs
1 & 3, too in this workbook.
Now it always opens up at sheet 3 - I still can't get it to open at sheet 1.
But I've got rid of the 65536-row irritation I mentioned in my original
post.

Your replies have been much appreciated but my problems here are quite
insignificant: I can easily live with them, and I'm not looking to you to
come back again. Isn't it just irritating when things don't behave just as
they should and you can't work out why?


Thanks again,
Don.

 

[MartinW]

Hi again Don,

I know you said it is non-consequential, but it still intrigues me that the
opening problem has moved to the new sheet.

Could it be a function that is volatile (like now() or today()) or
a macro that is still active when you save, so that it is the last thing to
be saved?

Copying and pasting by small increments would help to isolate
the rogue cell(s), if that is the case.

Or, like you say, we can just do a 'Ned Kelly' and say 'Such is Life"

Regards
Martin

 

[Don Guillett]

try a workbook_open event in the ThisWorkbook module

 

[Don Guillett]

right click the excel icon just to the left of FILE>view code>insert this.
SAVE

Private Sub Workbook_Open()
Sheets("sheet2").Select
End Sub

If this doesn't work send me your workbook.

=your private email to me
Thanks for replying to my post on the ng. But I really am an
"excel.newuser"!
I'm not now very concerned to take this matter further - life's too
short! - but feel I must reply to your post somehow.
If it won't take up too much of your time perhaps you might explain by
email (in a few words, to an old man) just what you mean by "try a
workbook_open event in the ThisWorkbook module"?

BW's,
DB.
(another Don)