CODEWITHSUNDEEP
excelvbaontime
Gmaster
Gmaster

Reputation: 30

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.

Upvotes: 0

Views: 957

Answers (3)

Matthias Fuglsang-Damgaard
Matthias Fuglsang-Damgaard

Reputation: 421

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

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

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

Upvotes: 0

Matthias Fuglsang-Damgaard
Matthias Fuglsang-Damgaard

Reputation: 421

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

Upvotes: 1

Related Questions