Phrase Data Into Sheets Excel VBA
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Long
If Target.Column = 8 Then
For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, "H").Value = "Administration" Then
Rows(i).Copy
Sheets("Administration").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
ElseIf Cells(i, "H").Value = "IT" Then
Rows(i).Copy
Sheets("IT").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
ElseIf Cells(i, "H").Value = "Human Resources" Then
Rows(i).Copy
Sheets("Human Resources").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
End If
Next i
End If
End Sub
Code Explanation:
Private Sub Worksheet_Change(ByVal
It happens when cells on the worksheet are Run VBA Script by the user or by an external link.
Dim A As Long
Declare the A variables.
If Target.Column = 8 Then
Set Column H For Target, Column Number Is 8
For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row
Loop For Row 5 to Last Row
If Cells(i, "H").Value = "Administration" Then
If Condition for administrator department
Rows(i).Copy
Current Row Copy
Sheets("Administration").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Set "Administrator" sheet as the Paste Value Only "Administrator" sheet
Rows(i).Delete
Current Row Delete
ElseIf Cells(i, "H").Value = "IT" Then
Another Else if Condition For IT Department
Rows(i).Copy
Sheets("IT").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
The Same Code In Above Paste Data To IT Sheet
ElseIf Cells(i, "H").Value = "Human Resources" Then
Rows(i).Copy
Sheets("Human Resources").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
The Same Code In Above Paste Data To Human Resources Sheet
End If
If the user does not expect to add additional statements, the if statement is completed by an End If.
Next i
running the (I) Loop the specified number of times
End If
End Sub
