Hi I am trying to create a macro that will copy rows from a list o
sheet 1 to sheet 2 based on a specific value (Column M = "Y"). I have
button that moves all Y values to the top of the list. I have code tha
will work with literal ranges. However there may be 1 or 10 rows tha
need to be copied and the next open row on Sheet 2 will be alway
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 prope
number of rows will be copied and be copied into the next blank spac
but it won't compile and since this is my first attempt at a macro
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
clue!
Any help would be greatly appreciated : - )
Thanks for your time

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