macro to copy changing range to next blank cell in another sheet

Hi,

Try the following if your data is all values rather than formulae.

Sub test()
Range(Sheets("Adult Sort").Range("A19"), Sheets("Adult Sort"). _
Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Adult Fiction").Range("A24")
End Sub

If the data are formulae and you want values then

Sub test()
Range(Sheets("Adult Sort").Range("A19"), Sheets("Adult Sort"). _
Range("A" & Rows.Count).End(xlUp)).Copy 

Sheets("Adult Fiction").Range("A24").PasteSpecial(xlPasteValues)
End Sub

I had originally thought that since you mentioned the next blank cell that you perhaps wanted to copy that blank cell too. Hence the reference to .Cells(2,1). I suspect that you only want to copy down to the last value in the range so the above does not need the .Cells(2,1) reference.

In the original formula the
Range("A" & Rows.Count)
evaluates to
Range("A1048576")
since there are 1048576 rows on Excel 2007/2010
The .End(xlUp) simply moves upwards from row 1048576 until it finds the next non blank value.
The .Cells(2,1) then just identifies a cell which is the second row and first column away from the reference cell. So for instance if the .End(xlUp) were to find cell A45, then adding .Cells(2,1) to the instruction will position you at A46. A .Cells(4,3) would position you at C49.

However why don't you upload your workbook and explain your ultimate goal. The fact that you have a sheet named Sort makes me think you may be able to achieve your aim without necessarily copying data elsewhere. It's often the case that people ignore Excel's ability to Filter or sort data in place.