How to return to sheet I came from?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Thanks Paul.........
Very interesting...........I got my answer I needed earlier, but I thank you
much for your response.

Vaya con Dios,
Chuck, CABGx3
 
Back
Top