...

MS Excel VBA

Phrase Data Into Sheets Excel VBA

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  
Close the second if condition and exit a sub-procedure

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top