Stop macro from switching between sheets

Anton1999

New Member
Joined
Aug 5, 2020
Messages
25
Office Version
  1. 2013
Platform
  1. Windows
Good afternoon everyone,

VBA Code:
Sub Stop_Timer()
        On Error Resume Next
        Application.OnTime Now + TimeValue("00:00:01"), "IncreamentTimer", schedule:=False
        If Err = 0 Then
        Application.ScreenUpdating = False
                ThisWorkbook.Sheets("Sheet1").Range("L9").Value = Format(Range("K8").Value - ThisWorkbook.Sheets("Sheet1").Range("K9").Value, "hh:mm:ss")
                ThisWorkbook.Sheets("Time Summary").Select
                ThisWorkbook.Worksheets("Time Summary").Range("E12").Select
                Selection.Copy
                ThisWorkbook.Worksheets("Time Summary").Range("H12").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                ThisWorkbook.Sheets("Sheet1").Range("J8").Value = "Timer OFF"
                ThisWorkbook.Sheets("Sheet1").Range("J8").Font.Color = vbBlack
               
        Else
         
                MsgBox "Timer is currently OFF.", vbExclamation, "Timer is OFF!"
   

                 End If
    
End Sub

When the value is pasted in the Time Summary sheet, Excel switches to that sheet, instead of just pasting the values and staying in the current sheet1 that I am in.
The screen should not jump to the Time Summary sheet.

Can someone please assist.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Stop_Timer()
        On Error Resume Next
        Application.OnTime Now + TimeValue("00:00:01"), "IncreamentTimer", schedule:=False
        If Err = 0 Then
        Application.ScreenUpdating = False
                ThisWorkbook.Sheets("Sheet1").Range("L9").Value = Format(Range("K8").Value - ThisWorkbook.Sheets("Sheet1").Range("K9").Value, "hh:mm:ss")
                ThisWorkbook.Worksheets("Time Summary").Range("E12").Copy
                ThisWorkbook.Worksheets("Time Summary").Range("H12").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
                ThisWorkbook.Sheets("Sheet1").Range("J8").Value = "Timer OFF"
                ThisWorkbook.Sheets("Sheet1").Range("J8").Font.Color = vbBlack
               
        Else
         
                MsgBox "Timer is currently OFF.", vbExclamation, "Timer is OFF!"
   

                 End If
    
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,791
Office Version
  1. 2019
Platform
  1. Windows
try

VBA Code:
Sub Stop_Timer()
    Dim wsSheet1 As Worksheet, wsTimeSummary As Worksheet
    
    With ThisWorkbook
        Set wsSheet1 = .Worksheets("Sheet1")
        Set wsTimeSummary = .Worksheets("Time Summary")
    End With
    
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "IncreamentTimer", schedule:=False
    
    If Err = 0 Then
    
        With wsSheet1
            .Range("L9").Value = Format(.Range("K8").Value - .Range("K9").Value, "hh:mm:ss")
            .Range("J8").Value = "Timer OFF"
            .Range("J8").Font.Color = vbBlack
        End With
        
        With wsTimeSummary
            .Range("E12").Copy
            .Range("H12").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                       SkipBlanks:=False, Transpose:=False
        End With
        Application.CutCopyMode = False
    Else
        
        MsgBox "Timer Is currently OFF.", vbExclamation, "Timer Is OFF!"
        
    End If
    
End Sub

Dave
 

Anton1999

New Member
Joined
Aug 5, 2020
Messages
25
Office Version
  1. 2013
Platform
  1. Windows
Fantastic! Fixed my problem, instantly. Thank you Dave.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,791
Office Version
  1. 2019
Platform
  1. Windows
Fantastic! Fixed my problem, instantly. Thank you Dave.

Pleased your issue resolved - we all work as a team here & we appreciate your feedback.

Dave
 

Forum statistics

Threads
1,141,402
Messages
5,706,245
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top