Going back to A1

DataMngr

New Member
Joined
Nov 23, 2015
Messages
17
I have an excel waiting list that uses VBA to copy and paste an entire row to another worksheet.

However, now when you try to navigate through the worksheet using <tab> or the arrow, it relocates me to A1 each and every time there is information put into a cell.

Is there any way to stop this and get it back to using tab and the over arrow?

Thanks,

Ashton
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Is there any code in the WorkSheet itself, like a change event?
Posting the code might help.
 
Last edited:

DataMngr

New Member
Joined
Nov 23, 2015
Messages
17
Yes, here is the code:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Complete").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 17).Value = "COMPLETE" Then
Rows(i).Copy Destination:=Sheets("Complete").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next

For b = Lastrowc To 1 Step -1
If Cells(b, 17).Value = "COMPLETE" Then
Rows(b).EntireRow.Delete
End If
Next
Cells(1, 1).Select
Application.ScreenUpdating = True
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Canceled").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 17).Value = "CANCEL" Then
Rows(i).Copy Destination:=Sheets("Canceled").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next

For b = Lastrowc To 1 Step -1
If Cells(b, 17).Value = "CANCEL" Then
Rows(b).EntireRow.Delete
End If
Next
Cells(1, 1).Select
Application.ScreenUpdating = True

With Sheets("Active")
Dim lr As Long
Set sh = ActiveSheet

Cells(1, 1).Select
Application.ScreenUpdating = True
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowb = Sheets("Pending").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Active").Activate
For i = 1 To Lastrow
If Cells(i, 17).Value = "PENDING" Then
Rows(i).Copy Destination:=Sheets("Pending").Rows(Lastrowb)
Lastrowb = Lastrowb + 1
End If
Next

For b = Lastrowc To 1 Step -1
If Cells(b, 17).Value = "PENDING" Then
Rows(b).EntireRow.Delete
End If
Next
Cells(1, 1).Select
Application.ScreenUpdating = True
End With

End Sub
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
It's because of the line...

Code:
Cells(1, 1).Select

...at the bottom of the SUb.

I hope this helps!

EDIT: If posting code it is good practice to use code tags, it makes the code much easier to read and debug.
 
Last edited:

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
You're welcome DataMngr, thanks for the feedback
Have a great weekend.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,884
Messages
5,598,651
Members
414,251
Latest member
oExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top