How to use a condition on a worksheet to copy a line from same [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I'm trying to use an input value on one worksheet to copy a line from one worksheet to another worksheet. Specifically- I'd like to copy a row of a worksheet when a specific cell on same row has a value of "Y". I'd like the spreadsheet to copy the entire row to become a line in another worksheet when this value is input. Is this possible? If so, how?

Cheers

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!