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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

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
Back
Top