How to return to sheet I came from?
 

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("Aut hor")
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

How to return to sheet I came from?
 

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("Aut hor")
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("Aut hor")
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

--
Charles Chickering

"A good example is twice the value of good advice."


"CLR" wrote:

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("Aut hor")
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

How to return to sheet I came from?
 

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


"CLR" wrote in message
...
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("Aut hor")
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

How to return to sheet I came from?
 

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



"Charles Chickering" wrote:

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("Aut hor")
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("Aut hor")
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

--
Charles Chickering

"A good example is twice the value of good advice."


"CLR" wrote:

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("Aut hor")
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

How to return to sheet I came from?
 

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

Vaya con Dios,
Chuck, CABGx3



"PCLIVE" wrote:

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


"CLR" wrote in message
...
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("Aut hor")
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

How to return to sheet I came from?
 

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