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

 

[JLGWhiz]

To do it with a formula.

=IF(Sheet1!$A2="A",Sheet1!B2,"")

put into cell B2 of the destination sheet, then drag across and down to copy the formula in all the cells you want to copy to.

The values on the destination sheet will automatically change as the values are changed on the source sheet, but not vice versa.

 

[My Aswer Is This]

Here is a Vba approach which will not leave you with any blank rows:
This is a Module script which will only run when you activate the macro in some manual way.

Sub FilterMini()
With Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[Throughstream]

Many thanks, is there a way to run the macro automatically when a value is put into Column G?

Cheers

 

[My Aswer Is This]

I do not see anything in your original post where you said you wanted it to run automatically when you enter a value into Column "G". And what value will you be entering into column "G" or do you mean any value.


So do you want the row deleted from the original sheet after it's copied over?
Please provide any more details you may not have specified.
Do you want just values copied over or formatting also?

And give me both exact sheet names

Like sheet named "Master"
And sheet named "Backup"

 

[Throughstream]

I do not see anything in your original post where you said you wanted it to run automatically when you enter a value into Column "G". And what value will you be entering into column "G" or do you mean any value.


So do you want the row deleted from the original sheet after it's copied over?
Please provide any more details you may not have specified.
Do you want just values copied over or formatting also?

And give me both exact sheet names

Like sheet named "Master"
And sheet named "Backup"

Hi appologies I should have provided more info.

i would like to be able to fill in the row and when column G is completed it then copies the data to a specific sheet.

The first sheet and second sheet will be master copy 1 and two respectively and after that Each sheet will have the "Name" of the person I.e Sheet3 will be named A and Sheet4 B etc etc

As an example once a a row is completed with A put in the name column and the rest of data inputted, once column G is entered I would like entire row to be copied to the Sheet named A. If the name is B the data would be copied to Sheet B.


I am extremely greatfull for your help.

regards

Dave

 

[My Aswer Is This]

You do understand this is completely different from what you originally said which was this:

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 <acronym title="visual basic for applications">vba</acronym>.

I think I now understand more but I still do not understand this:

The first sheet and second sheet will be master copy 1 and two respectively

When I ask for sheet names I want them showed exactly as I asked for in previous post.

I understand sheet names will be in column "A"

But what is the name of the Master sheet?????
I want to see the name like this:

"Master" do you understand?

And what about this second master sheet what do you want to do with this.

 

[My Aswer Is This]

I going to let someone else help you because I'm running out of time here:

And you never answered these two question either.

So do you want the row deleted from the original sheet after it's copied over?
Do you want just values copied over or formatting also?

 

[Throughstream]

Hi I'm sorry I'm new to excel so trying to answer as best I can.. I would like the cells to be copied with no formulae and not deleted form the sheet "Master Copy 1". "Master Copy 2" is just for another project and I don't want anything done with this.
I've done an example manually...

[/URL]EXAMPLE 3 by Dave Lindop, on Flickr[/IMG]

[/URL]EXAMPLE 2 by Dave Lindop, on Flickr[/IMG]

[/URL]EXAMPLE 1 by Dave Lindop, on Flickr[/IMG]

 

[JLGWhiz]

This is event code and will run when any change is made to the worksheet that hosts the code. It will only execute the copy action if the change is in column G.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim sh As Worksheet
Set sh = Sheets("Sheet2") 'Edit sheet name
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        If Target.Offset(, -6).Value = "A" Then
            Target.EntireRow.Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            Target.EntireRow.Delete
        End If
    End If
Application.EnableEvents = True
End Sub

When entries are made by the user the code checks if an entry is in column G. If there is an entry in column G, then the code looks at column A for the capital letter "A" and if found, it copies that row of data to sheet 2, then deletes the entry from sheet 1.
To install the code, right click the sheet name tab for the source sheet (sheet 1) and click 'View Code' in the pop up menu. When the VB Editor opens, copy the code above into the large code pane and save the file as a macro enabled workbook to preserve the code. Close the VB Editor and the code will now run as described above.