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
and paste them in list wise every 5 min in Sheet2
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.
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