The macro doesn't seem to work if the cells in column Y are pulling data from other cells (gets an error); therefore, I ran the copy and paste special macro BEFORE running the one you wrote.

But when I run the paste special macro and then the one you wrote, it copies and pastes all of the rows, not just ones with values in column Y.