copy a row range and paste to another sheet

[spycein]

Hi Everybody, actually i want to use vba to copy a range of data to another sheet.

For example i have two worksheets. Sheet one named Invoice List which contains a table as follows

Date Bill No. Amount Payament Status
1/8/14 1 100 pending
31/7/14 2 200 pending

so if i type "paid" in "payment status" column the the data of that row (start from column A to column C) except payment status column (column D) should copy and paste special to the sheet 2 named "Paid" and then delete that range.

Hope you guys understand my query.

Thank you in advance.

 

[VoG]

Right click the Invoice List tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
If LCase(Target.Value) = "paid" Then
    Application.EnableEvents = False
    Target.Offset(, -3).Resize(, 3).Copy Destination:=Sheets("Paid").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

Vog. After putting a script like this one you just posted in your sheet is their a way to put another script into the same sheet that is completely seperate from the first script. The page would look something like this. I have tried doing this but cannot get it to work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
If LCase(Target.Value) = "paid" Then
    Application.EnableEvents = False
    Target.Offset(, -3).Resize(, 3).Copy Destination:=Sheets("Paid").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub
[COLOR=#ff0000]If Target.Address = "$C$1" Then
Range("E:E").ClearContents[/COLOR]

 

[VoG]

You could combine the codes like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
    If LCase(Target.Value) = "paid" Then
        Application.EnableEvents = False
        Target.Offset(, -3).Resize(, 3).Copy Destination:=Sheets("Paid").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Delete
        Application.EnableEvents = True
    End If
ElseIf Target.Address = "$C$1" Then
    Application.EnableEvents = False
    Range("E:E").ClearContents
    Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

Thanks VoG

 

[My Aswer Is This]

Vog. I'm don't mean to bother you too much but my script doesn't work after adding the Else if statement. Here is the actual script I'm trying to make. The first part works find but not the ElseIf part highlighted in red. Thanks for all your help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFind As Range
If Target.Address = "$C$1" Then
    Application.EnableEvents = False
    Set rFind = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Target.Value)
    If rFind Is Nothing Then
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
    Else
        rFind.Offset(0, 1).Value = "Already exists"
    End If
    Application.EnableEvents = True
    Target.Select
[COLOR=#b22222]ElseIf Target.Address = "$C$4" Then
    Application.EnableEvents = False
    Range("A:B").ClearContents
    Application.EnableEvents = True
End If
[/COLOR]End Sub

 

[spycein]

Thank you vog.. it works like a charm........

 

[VoG]

Vog. I'm don't mean to bother you too much but my script doesn't work after adding the Else if statement. Here is the actual script I'm trying to make. The first part works find but not the ElseIf part highlighted in red. Thanks for all your help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFind As Range
If Target.Address = "$C$1" Then
    Application.EnableEvents = False
    Set rFind = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Target.Value)
    If rFind Is Nothing Then
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
    Else
        rFind.Offset(0, 1).Value = "Already exists"
    End If
    Application.EnableEvents = True
    Target.Select
[COLOR=#b22222]ElseIf Target.Address = "$C$4" Then
    Application.EnableEvents = False
    Range("A:B").ClearContents
    Application.EnableEvents = True
End If
[/COLOR]End Sub

Sorry, I didn't realise that you had replied. Not sure why it doesn't work but try deleting

Target.Select

 

[My Aswer Is This]

Vog:
Removing the Target.Select did not correct the problem.
I see we have
2 if statements
1 Else statement
2 End if statements
1 ElseIf statements
Is all that OK?

 

[VoG]

The code compiles OK and the ElseIf part works for me.