How to return to previously viewed worksheet?

[theredfoxisquick]

I want to be able to return to the worksheet I was looking at when I activate coding that takes me away from the currently viewed worksheet. I tried this, but it is now working. Anyone know how to fix this?

[FONT=verdana] [COLOR=#006400]'in ThisWorkbook
[/COLOR][COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Workbook_SheetDeactivate([COLOR=blue]ByVal[/COLOR] Sh [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR])     
[COLOR=blue]Set[/COLOR] LstSht = Sh [COLOR=blue]End Sub[/COLOR]  
 [COLOR=#006400]'in Standard Module
[/COLOR][COLOR=blue]Public[/COLOR] LstSht [COLOR=blue]As[/COLOR] Worksheet  
[COLOR=blue]
Sub[/COLOR] GoToLast()     LstSht.Activate
 [COLOR=blue]
End Sub[/COLOR] [/FONT]

 

[Andrew Poulsom]

This worked for me:

'ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set LastSht = Sh
End Sub

'General module

Public LastSht As Worksheet

Sub GoToLast()
    If Not LastSht Is Nothing Then
        LastSht.Activate
    End If
End Sub

 

[rollis13]

Cross-post: How to return to the previously viewed worksheet?

 

[Ringhal]

Or:

Dim wkSt As String
wkSt = ActiveSheet.Name
'your code here
Sheets(wkSt).Select

 

[theredfoxisquick]

This worked for me:

'ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set LastSht = Sh
End Sub

'General module

Public LastSht As Worksheet

Sub GoToLast()
    If Not LastSht Is Nothing Then
        LastSht.Activate
    End If
End Sub

Does it matter if the GoToLast Sub needs to be called from a userform? I still can't get it to work.

 

[Andrew Poulsom]

Please post your code that doesn't work.

 

[theredfoxisquick]

'userform

Private Sub CommandButton1_Click()
Dim counter As Integer
Dim timeKeeper As Integer
Dim paid As Integer

Application.ScreenUpdating = False

counter = 1
paid = 0
Do Until Worksheets(1).Cells(counter, 1) = ""
counter = counter + 1
Loop

For timeKeeper = 1 To counter Step 1
If Worksheets(1).Cells(timeKeeper, 1) >= CDate(Me.TextBox1) And Worksheets(1).Cells(timeKeeper, 1) <= CDate(Me.TextBox2) Then
paid = paid + 1
End If
Next
Me.Label4.Caption = "There are " & paid & " unpaid records to process in this date range."


For timeKeeper = 1 To counter Step 1
If Worksheets(1).Cells(timeKeeper, 1) >= CDate(Me.TextBox1) And Worksheets(1).Cells(timeKeeper, 1) <= CDate(Me.TextBox2) Then
Worksheets(4).Cells(timeKeeper - 1, 1) = Worksheets(1).Cells(timeKeeper, 2)
Worksheets(4).Cells(timeKeeper - 1, 3) = Worksheets(1).Cells(timeKeeper, 3)
Worksheets(4).Cells(timeKeeper - 1, 4) = Worksheets(1).Cells(timeKeeper, 4)
End If
Worksheets(4).Range("A1", Worksheets(4).Range("A1").End(xlDown)).Copy Destination:=Worksheets(4).Range("B1")
Worksheets(4).Range("B1", Worksheets(4).Range("B1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
Next

Application.EnableEvents = False
Worksheets(4).Range("B1", Worksheets(4).Range("B1").End(xlDown)).Copy
Worksheets(3).Select
Worksheets(3).Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Worksheets(3).Paste


GoToLast


Dim counter2 As Integer

counter2 = 1

Do While Worksheets(4).Cells(counter2, 2) <> ""
counter2 = counter2 + 1
Loop

For timeKeeper = 1 To counter Step 1
If Worksheets(1).Cells(timeKeeper, 1) >= CDate(Me.TextBox1) And Worksheets(1).Cells(timeKeeper, 1) <= CDate(Me.TextBox2) And Worksheets(1).Cells(timeKeeper, 2) = Worksheets(4).Cells(counter2, 2) Then
Worksheets(4).Cells(counter2, 3) = Worksheets(4).Cells(counter2, 3) + Worksheets(1).Cells(timeKeeper, 4) - Worksheets(1).Cells(timeKeeper, 3)
End If

Next


End Sub

'module 1
' asks for object when it gets to If Not LastSht Is Nothing Then
Public LastSht As Worksheet


Sub GoToLast()
If Not LastSht Is Nothing Then
LastSht.Activate
End If
End Sub

'this workbook

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set LastSht = Sh
End Sub

 

[Andrew Poulsom]

Are you getting an error, because I don't in Excel 2010? Nothing happens if a sheet hasn't been deactivated. Simplified code:

'UserForm

Private Sub CommandButton1_Click()
    GoToLast
End Sub

'Module 1

Public LastSht As Worksheet

Sub GoToLast()
    If Not LastSht Is Nothing Then
        LastSht.Activate
    End If
End Sub

'ThisWorkbook

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set LastSht = Sh
End Sub

But why would you need to use it in a UserForm?