MrExcel Publishing
Your One Stop for Excel Tips & Solutions

worksheet


Posted by rosemary on October 18, 2000 7:47 AM


I have a workbook with several worksheets
titled by date. After I enter figures, I
save the file and close. When I re-open the
file it does not open to the last worksheet
that was used. How can I get the last sheet
worked on to open ????
I know I can point to it and open it up but
it would be more convenient to open where I
left off.


Posted by Ben O. on October 18, 2000 9:44 AM

Try this. I didn't test it:

Sub Auto_Open()
Set LastSheet = Sheets("Sheet1").Range("Memo")
Sheets(LastSheet).Select
End Sub

Sub Auto_Close()
Set LastSheet = Sheets("Sheet1").Range("Memo")
LastSheet.Value = ActiveSheet.Name
End Sub

You will have to create a named range called "Memo" on Sheet1 (or whatever sheet you want, just change "Sheet1" to the name of the sheet in the code). Make it one cell and format it as Text.

The Auto_Close macro will put the name of the active sheet into Memo. The Auto_Open macro will go to the name of the sheet in Memo when you open the workbook.

I hope it works,

-Ben

I have a workbook with several worksheets titled by date. After I enter figures, I save the file and close. When I re-open the file it does not open to the last worksheet that was used. How can I get the last sheet worked on to open ???? I know I can point to it and open it up but it would be more convenient to open where I left off.

Posted by Ben O. on October 18, 2000 10:00 AM

Okay, that won't work, but this will...

This has been tested:

Sub Auto_Open()
Dim LastSheet As String
LastSheet = Sheets("Pay Period").Range("MEMO")
Sheets(LastSheet).Activate
End Sub

Sub Auto_Close()
Set LastSheet = Sheets("Pay Period").Range("MEMO")
LastSheet.Value = ActiveSheet.Name
End Sub


My version of Excel selects the last sheet worked on without this code though. Perhaps it's new to Excel 2000?

-Ben

Set LastSheet = Sheets("Sheet1").Range("Memo") Sheets(LastSheet).Select End Sub Set LastSheet = Sheets("Sheet1").Range("Memo") LastSheet.Value = ActiveSheet.Name End Sub : : I have a workbook with several worksheets : titled by date. After I enter figures, I : save the file and close. When I re-open the : file it does not open to the last worksheet : that was used. How can I get the last sheet : worked on to open ???? : I know I can point to it and open it up but : it would be more convenient to open where I : left off.

Posted by casey on October 18, 2000 3:21 PM

I have a workbook with several worksheets titled by date. After I enter figures, I save the file and close. When I re-open the file it does not open to the last worksheet that was used. How can I get the last sheet worked on to open ???? I know I can point to it and open it up but it would be more convenient to open where I left off.

i'm not for sure if you wanted "code" as your answer before, but if not, you can just be sure to be on the worksheet that you want it to open up on when you save your file. i also work off of a multiple tabbed worksheet, and i save the file where i left off, and it opens for me in the same spot every time