I currently have a sheet that is alphabetizing itself when a name is entered into the B column. header are on row 2 of sheet. the information in each row needs to stay with each name. right now the last two columns are not stay with their corresponding rows meaning they are being added to a row they do not belong to. My spreadsheet goes from A to AP. columns AL and AM or not stay with their rows. my codes are below:
mod 2
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
If Target.Value <> "Z" Then Exit Sub
Dim lRow As Long
lRow = Sheets("REMOVED").Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
With Target.EntireRow
.Copy Sheets("REMOVED").Cells(lRow, 1)
.Delete xlUp
End With
End If
sheet (current)
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("CURRENT")
Set ws2 = Worksheets("REMOVED")
Dim LRowCUR As Long, LRowREM As Long
LRowCUR = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
LRowREM = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
If Target.Cells.CountLarge = 1 And Not Intersect(Range("A3:B" & LRowCUR), Target) Is Nothing Then
Select Case Target.Column
Case Is = 1
If Target.Value = "Z" Then
Application.EnableEvents = False
With Target.EntireRow
.Copy ws2.Cells(LRowREM, 1)
.Delete xlUp
End With
ws2.Range("B3").CurrentRegion.Sort Key1:=ws2.Range("B3"), order1:=xlAscending, Header:=xlYes
End If
Case Is = 2
Application.EnableEvents = False
ws1.Range("B2").CurrentRegion.Sort Key1:=ws1.Range("B2"), order1:=xlAscending, Header:=xlYes
End Select
End If
Application.EnableEvents = True
End Sub
i do not which one the issue is on please help!!