Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return to sheet I came from?
Also check out "Code for Returning to Last Active Worksheet" thread.
Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet selected, automatic return to top of sheet | Excel Worksheet Functions | |||
lookup single value in one sheet, return multiple results from theother sheet | Excel Worksheet Functions | |||
Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
How can I cause a cell to return the sheet name i.e. sheet 1? | Excel Worksheet Functions | |||
how return to last used sheet? | Excel Programming |