Q:

Copy variable range on one sheet to next empty row on another sheet

I need to copy all data in columns P:Y and paste it into the next blank cell in column A.
I've written the code below, which works fine, except that the data it is copying (P:Y) contains a formula down the whole column and although I am pasting values in order to only get the value returned, it is counting all cells as containing data.
By this I mean that when I run the macro the first time, it works. But when I run it a second time, (which I need to do) when it finds the last empty row. It isn't the last empty row! It selects a row far below the actual last row that I can see data in.
There is no data in the empty rows and no formula in them, but for some reason that is beyond me, it is treating the rows as not empty.
The data returned in columns P:Y by the formula will change every month, so I can't define a specific range.
How can I modify the code to rectify this - or is there a better way I could do this?
Sub SelectRangea()

   Sheets("Set Up Data").Select
   Range("P2:Y10000").Select
   Application.CutCopyMode = False
   Selection.Copy

With Sheets("Pasted Report")
   lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
   .Range("A" & lst).PasteSpecial xlPasteColumnWidths
   .Range("A" & lst).PasteSpecial xlPasteValues

End With

End Sub

A:

When a cell contains a formula, it will have a value, even though it seems empty when inspecting the cell on the sheet.  So if you copy such seemingly empty cells and paste them somewhere else (even if only as values), the pasted-to cells now do contains something (i.e. they're not really empty anymore; they're an empty string to be exact instead of VBA's Empty).
Range.End will then move to those last empty cells instead of moving to the last true filled cell.
You can see this for yourself if you do this by hand on an empty sheet: enter in A1:A5 the formula ="", then copy the range as values to B1:B5, then select B10 and press Ctrl-Up.
A fix would be to use the Range.End method, and then just inspect the cell contents going upwards.  Or use Range.Find, but keep in mind that that also alters the settings in the user's Find dialog.

