Copy a row to another sheet based on value in column A

[titushanke]

Hi,

I would like to copy rows of data from one worksheet to another (destination worksheet exists, would need to be cleared). The source worksheet contains a "Y" or "N" in column A.


FOR A4 to Q99, filter all rows that have value "Y" in column A of their respective row.

Go to the destination sheet, erase all data in the range A4:Q99.

Paste the filtered data from the source sheet.


Could anybody help me with this in VBA? I don't want to use the filter function as it's many work sheets and I would like to automate it..

Thanks, Titus

PS: Ideally the paste operation should be "paste values", due to existing formatting in the destination sheet.

 

[nightcrawler23]

Just record what you want to do. From your explanation it seems youi wont have to change any code. If required post the code. Will take a look.

 

[nightcrawler23]

Below is what i recorded for my data in Sheet1 Columns A:C pasting the result in sheet2. i had to modify only one line in this code. modify as per your need and i hope it works for you.

Sub FilterData()

    Columns("A:C").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$72").AutoFilter Field:=1, Criteria1:="y"
    Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Columns("A:C").Select
    ActiveSheet.Range("$A$1:$C$72").AutoFilter Field:=1
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub