How to use a condition on a worksheet to copy a line from same
Closed
                                    
                        Bill                    
                                    -
                            Nov  5, 2014 at 07:10 PM
                        
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Nov 6, 2014 at 05:56 PM
        vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Nov 6, 2014 at 05:56 PM
        Related:         
- How to use a condition on a worksheet to copy a line from same
- Transfer data from one excel worksheet to another automatically - Guide
- Add worksheet vba - Guide
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
- Copy worksheet multiple times and rename ✓ - Excel Forum
- Excel conditional formatting based on date - Guide
1 response
                
        
                    vcoolio
    
        
                    Posts
            
                
            1411
                
                            Registration date
            Thursday July 24, 2014
                            Status
            Moderator
                            Last seen
            September  6, 2024
            
            
                    262
    
    
                    
Nov 6, 2014 at 05:56 PM
    Nov 6, 2014 at 05:56 PM
                        
                    Hello Bill,
Try the following code. I think its what you may be after. I'm assuming that Column A is where you'll be entering the "Y" value.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Dim Rng As Range
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
With Rng
.AutoFilter , field:=1, Criteria1:="Y"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A1")
.AutoFilter
End With
End Sub
You will need to change the name "Sheet2" to exactly whatever your tab name is.
Right click on the tab name (Sheet1) and then click on "view code". Then, in the big white field, paste the above code. Once you enter "Y" in a row of Column A of Sheet1, your entire row entries from Sheet1 should then be copied to Sheet2.
If there are no "Y" values in any row in Column A, then nothing will be copied to Sheet2.
I hope this helps you out.
Cheers,
vcoolio
            Try the following code. I think its what you may be after. I'm assuming that Column A is where you'll be entering the "Y" value.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Dim Rng As Range
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
With Rng
.AutoFilter , field:=1, Criteria1:="Y"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A1")
.AutoFilter
End With
End Sub
You will need to change the name "Sheet2" to exactly whatever your tab name is.
Right click on the tab name (Sheet1) and then click on "view code". Then, in the big white field, paste the above code. Once you enter "Y" in a row of Column A of Sheet1, your entire row entries from Sheet1 should then be copied to Sheet2.
If there are no "Y" values in any row in Column A, then nothing will be copied to Sheet2.
I hope this helps you out.
Cheers,
vcoolio
