Stop macro from shifting sheets-CodePudding

When the below macro runs every 5 min, no matter on which Sheet I'm working, it just always comes/shifts to Sheet2.

I'm aware that .select in my code might be responsible for it, but is there any other way to write the below code or anything that can prevent from shifting and keep myself working on the sheet where I'm working.

It is very frustrating to change to another sheet every 5 min of 4-5 working hours on it.

I used (but no result)

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 in the macro but avoid it, as the original macro runs perfectly on my PC

CodePudding user response：

As Sheets ("Sheet2").Select is the last command where you select a sheet. it will always end up at Sheet2.

If you want to return to the sheet where you were. You should save the sheet name in a vaiable at the start. and then select the sheet again in the end

Sub test()
    Dim activeSheetName As String
    activeSheetName = ActiveSheet.Name
    Sheets("Sheet2").Select

'DO WHAT YOU WANT

    Sheets(activeSheetName).Select
End Sub

CodePudding user response：

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

CodePudding user response：

Instead of using select which is vulnerable to user interruption you could refer to the sheets as variables. this way is faster to run and you are sure which sheet is being used at any time. by using select you might lose track on which sheet is active at a any given point in your code.

Sub test()
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet

    Set Sheet1 = Sheets("Sheet1")
    Set Sheet2 = Sheets("Sheet2")

    Sheet1.Range("A1:D1").Copy Sheet2.Range("A1")
End Sub

With this method you can be on any sheet you want and the code will newer move to another sheet but the changes will stile be made