Software

Ask something
Discussion Locked

Copying data to a changing spreadsheet

0 helpful
5 replies

by gcheels

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I would like to copy a row of data, go to another sheet and paste it at the bottom of several rows of data. The rows of data will change each time I do this, so I can't use an absolute cell value to paste to. In older versions I could write the macro to go to the top row of data, do {END}{DOWN} {DOWN},then paste. How do I do this inside a macro. Thanks.

5 replies

by firstpeter

You could use a for...next loop to find the ending data. The example below assumes that there is always data in column A:

For i = 1 to 10000
If Range("A" & i).Value = "" Then
Rows(i).PasteSpecial xlPasteValues
Exit For
End If
Next i

That will start on row 1 and move through row 10000, searching for the first blank in column A. When it finds that, it pastes the information that was copied (code for that not shown here - can help you if needed) to that row, then ends the loop.

Does that help?

by firstpeter

You can change the code to look for values in column C (instead of A) and paste the results in the first blank row, or you can modify it to require a number of columns in a row are populated. You can feel free to e-mail me the spreadsheet and I'll see how I can help.

by gcheels

Thanks for your fast response, however, I'm having trouble with it. I enter "TEST" in cell C1, right-click and copy, then run the macro with your code and it pastes it in cell A1. I've tried it several different ways, with varying results. Thanks

This discussion is closed to new replies.