excelvbaontime

Stop macro from shifting sheets


The below macro runs every 5 minutes. No matter on which Sheet I'm working, it shifts to Sheet2.

.select might be responsible. Is there another way to write the code or prevent shifting, to keep on the sheet where I'm working.

I tried

Application.ScreenUpdating = False
' My macro code here
Application.ScreenUpdating = True

What my macro does

It copies data from some cells of Sheet1
Pic1

and paste them in list wise every 5 min in Sheet2
Pic2

My macro code

Sub Macro5()

'Code for running it every 5 min
Application.OnTime Now + TimeValue("00:05:00"), "Macro5", True

'Go to sheet1
Sheets ("Sheet1").Select

'Select some cell
Range ("A1:D1").Select

'Go down with CTRL + ↓
Selection. End (xlDown).Select

'Select 1 cell down using relative reference
ActiveCell.offset (1, 0).Range ("A1").Select

'Come to sheet2
Sheets ("Sheet2").Select

'Select some cells
Range ("I4:L4").Select

'Copy selected cells
Selection.Copy

'Come to sheet1
Sheets ("Sheet1").Select

'Paste that selected value in some cells of sheet 1
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False

End Sub

There might be some spelling error but ignore it, as the macro runs perfectly on my PC.


Solution

  • I'm a bit confused by your code.
    You move about on Sheet1, eventually selecting the first cell in column A below whatever data is in there.
    You then move to sheet 2, copy the range I4:L4, select sheet 2 again (which is already selected) and then paste the values & number formats over cells I4:L4.

    Your description says it's copying cells I4:L4 from sheet 1 to the bottom of the data on sheet 2.

    Sub Macro5()
    
        'Code for running it every 5 min
        Application.OnTime Now + TimeValue("00:05:00"), "Macro5", True
    
        'ThisWorkbook is the file that the code is in.
        ThisWorkbook.Worksheets("Sheet1").Range("I4:L4").Copy
        
        'Each reference starting with a . will reference ThisWorkbook.Worksheets("Sheet1")
        With ThisWorkbook.Worksheets("Sheet2")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
        End With
    End Sub  
    

    I've used End(xlUp) as End(xlDown) can jump to the end of the sheet if the sheet is empty.

    Also worth having a read of how-to-avoid-using-select-in-excel-vba