Hi Niffer
Try the following, it uses an autofilter to subset your records, determine
how many records are displayed (where column M) is set to Y, then copies
them into the next available row on sheet 2.
A few assumptions I made:
1. Your data is on sheet1 starts in row1 and uses columns A to M
2. You wish to copy all columns after filtering to required rows
3. I use column M to determine the number of selected rows on sheet1 and
sheet2

Sub SelectiveCopy()
Dim LastRow1 As Long, LastRow2 As Long
Sheets("Sheet1").Activate
Range("A1:M1").AutoFilter
Range("A1:M1").AutoFilter Field:=13, Criteria1:="Y"
LastRow1 = Worksheets(1).Cells(Rows.Count, "M").End(xlUp).Row
LastRow2 = Worksheets(2).Cells(Rows.Count, "M").End(xlUp).Row
Range("A1:M" & LastRow1).Copy Destination:=Sheets(2).Range("A" &
LastRow2)
Range("A1:M1").AutoFilter
End Sub

Cheers
Nigel


"Niffer " wrote in message
...
Hi I am trying to create a macro that will copy rows from a list on
sheet 1 to sheet 2 based on a specific value (Column M = "Y"). I have a
button that moves all Y values to the top of the list. I have code that
will work with literal ranges. However there may be 1 or 10 rows that
need to be copied and the next open row on Sheet 2 will be always
changing.

Worksheets("Sheet 1").Range("A19:D20").Copy _
Destination:=Worksheets("Sheet 2").Range("A10:D11")

I have tried to use calculated variables that will ensure the proper
number of rows will be copied and be copied into the next blank space
but it won't compile and since this is my first attempt at a macro I
cannot figure out how to fix this.

Worksheets("Sheet 1").Range(Cells(19, 1), Cells(test, 4)).Copy _
'Destination:=Worksheets("Sheet 2").Range(Cells(test2, 1), Cells(test3,
4))
I am assuming there is a much easier way to do this but I do not have a
clue!
Any help would be greatly appreciated : - )
Thanks for your time!


---
Message posted from http://www.ExcelForum.com/