Copy Row with condition

[Throughstream]

Hi I have the following set up for an annual leave style spreadsheet.

Sheet1

I would like to copy into sheet2 only the rows that have A under the name, is there a formula or do I have to use vba.

Cheers

Dave

 

[My Aswer Is This]

Try this:

When you enter some value into column "G" this script will run
It will copy the active row into the sheet named in column "A"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named "Master Copy 1"
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
On Error GoTo M
Dim ans As String
Dim Lastrow As Long
ans = Cells(Target.Row, 1).Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy
Sheets(ans).Rows(Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Exit Sub
M:
MsgBox "No such sheet exist"
End If
End Sub

 

[My Aswer Is This]

Now you need to know the minute you enter a value into column "G" the script will run.

So if you put a value in column "G" and then put a value in column "M" the data in column "M" will not be copied over.

And the value put in column "G" must be entered manually and not as the result of a formula.

 

[JLGWhiz]

Just re-read all the posts and I think this might be what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim nm As String
Application.EnableEvents = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        nm = Target.Offset(, -6).Value
            If nm <> "" Then
                Target.EntireRow.Copy 
                Sheets(nm).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            End If
    End If
Application.EnableEvents = True
End Sub

 

[Throughstream]

Thank you both so much it now works perfectly!!!!!!!



Cheers

Dave

 

[My Aswer Is This]

Well glad we got this question answered.

Thank you both so much it now works perfectly!!!!!!!



Cheers

Dave

 

[Throughstream]

Well glad we got this question answered.

Can I just ask if I in the future want to change the "Name" Column to B rather than A what do I change in your macro code ?

Cheers

Dave

 

[My Aswer Is This]

Try changing this line of code

ans = Cells(Target.Row, 1).Value

To this:

ans = Cells(Target.Row, [COLOR=#FF0000]2[/COLOR]).Value

The "2" means column (2)

 

[Throughstream]

Hi many thanks, is there a way of it only copying the row if the "date requested" falls between 01/04/2017 (April the first) to 31/03/2018 (end of March). Any date requested out side wouldn't get copied but wouldn't come up with an error.

Cheers

Dave