How to return to sheet I came from?

[Guest]

Hi All.......
Small macro writes to bottom of list on hidden sheet....works fine, except I
don't know how to return to the sheet I started from.....it just wants to
return me to a sheet next to the freshly hidden one. I do not know the name
of the starting sheet, so cannot hard code it in.

Sub OpenLog()
Dim k
k = ActiveWorkbook.BuiltinDocumentProperties.Item("Author")
Worksheets("Openlog").Visible = True
Worksheets("Openlog").Select
Range("OpenLog!a65000").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.Value = Date & ", " & Time
Selection.Offset(0, 1).Select
Selection.Value = k
Worksheets("Openlog").Visible = False
End Sub

Any help would be much appreciated.
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

In answer to your question you can get the ActiveSheet Name before you leave
then go back when your done.
Sub OpenLog()
Dim ws As Object
Dim k
Set ws = ActiveSheet
k = ActiveWorkbook.BuiltinDocumentProperties.Item("Author")
Worksheets("Openlog").Visible = True
Worksheets("Openlog").Select
Range("OpenLog!a65000").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.Value = Date & ", " & Time
Selection.Offset(0, 1).Select
Selection.Value = k
Worksheets("Openlog").Visible = False
ws.Activate
End Sub

However... I wouldn't even bother activating the other sheet. Try this
instead, it should run quicker:
Sub OpenLog()
Dim k
Dim r As Range
k = ActiveWorkbook.BuiltinDocumentProperties.Item("Author")
With Worksheets("Openlog")
Set r = .Range("A" & .Rows.Count).End(xlUp)
End With
r.Offset(1) = Date & ", " & Time
r.Offset(1,1) = k
End Sub

 

[PCLIVE]

Hi Chuck,

I recently played around with something like this and it appeared to work.
The information below was a response I had given someone else. You may or
may not find it useful.

First, create a sheet named "Data". This sheet can be hidden if you'd like.
Next, press Alt+F11 to bring up the VB editor.
Double-click on "ThisWorkbook".
Paste the following code:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sheets("Data").Range("A2").Value = Sheets("Data").Range("A1").Value
Sheets("Data").Range("A1").Value = ActiveSheet.Name
End Sub

Next, right-click "ThisWorkbook" again and goto "Insert" and then select
"Module".
Paste the following code:

Sub ActivateLastSheet()
Sheets(Sheets("Data").Range("A2").Value).Activate
End Sub

Close the VB Editor window.
From your Excel window, click Tools-Macro-Macros.
Select ActivateLastSheet and then click Options.
In this section you can assign a short-cut key to easily access the code
that activates the previous sheet you were on.


Hope this is helpful.
Paul

 

[Guest]

Thanks Charles (nice name)
Thanks very much for both suggestions. I appreciate your more efficient
version, but doubly so you taking the time to support my feeble code. You're
a Gentleman and a Scholar.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks Paul.........
Very interesting...........I got my answer I needed earlier, but I thank you
much for your response.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Also check out "Code for Returning to Last Active Worksheet" thread.
Garry