Copy an entire row based on conditions in a cell , please !!

[LIVERBIRD]

Hi People, as this is my first post I don't want to sound dumb, so i'll make it as painless as possible. I have a workbook with an "Data entry sheet" and a "Copied data sheet". Is there a way to copy and paste the row where one cell has a "Y" in it automatically . I have explained inside the worksheet itself that while it is probable that a macro could do it , is it possible to do it without a macro ?


Cheers to all in advance !!

 

[Fishboy]

Hi People, as this is my first post I don't want to sound dumb, so i'll make it as painless as possible. I have a workbook with an "Data entry sheet" and a "Copied data sheet". Is there a way to copy and paste the row where one cell has a "Y" in it automatically . I have explained inside the worksheet itself that while it is probable that a macro could do it , is it possible to do it without a macro ?


Cheers to all in advance !!

Hi LIVERBIRD, welcome to the boards.

Unfortunately formulas cannot carry out actions such as copy and pasting, you would definitely need to use a macro for that.

Having said that the VBA required to copy a row with a Y in a certain column is relatively simple. If you can let us know what column the Y is going to be in, and whether you want the code to look at an entire range of data and copy EVERY row where there is a Y value that would be very helpful.

 

[Fishboy]

This code for example would check column F of each row on the Data Entry Sheet and if the value is Y it copies the row to the next sheet. The code does assume that you already have your header row in place on the Copied Data Sheet.

Test this out in a COPY of your workbook. The following code is added to a standard module and can be run either by creating a button and applying it to it, or by pressing ALT+F8 in Excel and selecting the CopyRowsWithY macro:

Sub CopyRowsWithY()
' Defines variables
Dim Cell As Range, cRange As Range
    ' Defines the last row of both sheets based on column A
    LastRowDes = Sheets("Data Entry Sheet").Cells(Rows.Count, "A").End(xlUp).Row
    LastRowCDS = Sheets("Copied Data Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' Sets the range to be checked as column F on the Data Entry sheet (amend the column letter as required)
        Set cRange = Sheets("Data Entry Sheet").Range("F2:F" & LastRowDes)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value is "Y" then...
                If Cell.Value = "Y" Then
                    ' Copy the entire row to the first blank row of other sheet
                    Cell.EntireRow.Copy Sheets("Copied Data Sheet").Range("A" & LastRowCDS).EntireRow
                        ' Increase LastRowCDS by 1 to account for the new data
                        LastRowCDS = LastRowCDS + 1
                End If
            ' Check the next cell in the check range
            Next Cell
End Sub

 

[LIVERBIRD]

Hi Fishboy,
Thanks for your reply . I tried the macro (I think i.m doing it right) but it brings up an error every time ( LastRowCDS = Sheets("Copied Data Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1)

By the way, the column that has the letter "Y" is column G. I wish I could upload the workbook for you to see, but I cannot find the button to do so. It would be a lot easier I think for you to see it.
Cheers again.

---

---