copy cell content to other sheet if not empty, HOWTO?

[Lexje]

Hi Everybody,

I am looking for code to copy the contents of a range of cells to an equal cell range on another sheet, and do this while the original cel is not empty.

Example:

Sheets("Country_1999")
Look through column A:A
While Not IsEmpty(ActiveCell)
Copy contents from Row containing ActiveCell (A:AK) to destination Sheets("DestinationSheet").Same Row
ActiveCell.Offset(1,0).Select
Wend

How can I do this?

Thanks a lot for your help!

Erwin

 

[Andrew Poulsom]

Would it be?

ActiveCell.Resize(1, 37).Copy Sheets("DestinationSheet").Cells(ActiveCell.Row, 1)

 

[Lexje]

Andrew, thanks a lot!
That's exactly it, clear and simple!

One more question,
How do I go about to run this code without activating the source sheet?
Can this be done?

If I run this code now, while another sheet is active, I get error 1004 Application-defined or object-defined error.


Next, if we take this one step further, and have the formula iterate through 10 different sheets, how to
1. modify the destination to append the copied cells to the first empty row.
2. add a condition to check wheter contents of the 2nd cell of the destination row contains a value = 1999 (e.g.)

Thank you!

Erwin