Stop macro from shifting sheets

Question

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.

stop using SELECT !! This is a remark on most VBA question. SELECT is something that the recorded generate, which should be removed in most situations. — iDevlop, May 30 '22 at 13:50

Matthias Fuglsang-Damgaard · Answer 1 · 2022-05-30T12:57:04.470

1

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

edited May 30 '22 at 12:57

answered May 30 '22 at 12:52

Matthias Fuglsang-Damgaard

303
2
15

Thanks, but the code is a bit hard to get into the mind of a newbie like me. Btw, it works good and it is a universal code. – Gmaster May 30 '22 at 17:38

Matthias Fuglsang-Damgaard · Answer 2 · 2022-05-30T13:38:50.440

0

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

edited May 30 '22 at 13:38

answered May 30 '22 at 13:18

Matthias Fuglsang-Damgaard

303
2
15

FYI, the three default sheets that come in your workbook are already codenamed Sheet1, Sheet2 and Sheet3. Each sheet has a codename, which can be used in your VBA scripts to refer to that sheet without needing to define your own variable for it. Look at the project explorer to see the codenames of all the sheet and module objects in your workbook. – Toddleson May 30 '22 at 14:00

score 0 · Accepted Answer · answered May 30 '22 at 13:32

0

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

answered May 30 '22 at 13:32

Darren Bartrup-Cook

18,362
1
23
45

Apologies! The last 3rd line of code was wrong, I types `sheet1` instead of `sheet2`. I'm doing experiments with all answers. Btw, Thanks for your interest! – Gmaster May 30 '22 at 16:54
1

Exactly! This is what I was craving for and your 3rd code line (after timer) is just a slap on me what the rubbish things I was doing. Thanks professor (you look like my Economics professor). The code was still creating trouble by flashing some shifts of sheets. So Finally, i added this and works smooth. `Sub Macro5` `Application.ScreenUpdating = False` 'your given code. `Application.ScreenUpdating = True` `End Sub` – Gmaster May 30 '22 at 17:46
**Question:** Actual name of my `sheet1` and `sheet2` is `Analysis` and `Record`, respectively but when I use in code as `Sheet1` & `Sheet2` it gives me *RunTime error 9 (Subscript out of range)* While using those names (`Analysis` and `Record`) code works perfectly. Why? Any solution to use those Sheet1 and Sheet2 without error. – Gmaster May 30 '22 at 17:57
`Worksheets("Sheet1")` is a reference to the sheet with the tab name `Sheet1`, so your references should be `Worksheets("Analysis")` and `Worksheets("Record")`. Each sheet also has a codename that can only be changed in the Visual Basic Editor. In the Project Explorer in the Visual Basic Editor your sheet will appear as `Sheet1(Analysis)`. Sheet1 here is the codename and you can reference it using something like `Sheet1.Range....` rather than `Worksheets("Analysis").Range...` – Darren Bartrup-Cook May 31 '22 at 07:15

Stop macro from shifting sheets

3 Answers3