Paste problems with VBA code

[chris01252]

I would appreciate some guidance on what may be wrong with the code below. Basically what I am trying to do is copy a date from one single cell then paste it into multiple cells depending on the space available. The error I get seems to indicate the problem is related to the paste range is a different size to the copy range, but not sure how to overcome this.

	Column A	Column B
row1	01/01/15	ABC
row2	01/01/15	DEF
row3		GHI
row4		JKL
row5		MNO

The code is;

Please Login or Register  to view this content.

So if I had copied a date e.g. 15/07/15, then applying to example above I want this to be pasted into the range A3:A5.

Any pointers would be most helpful.

[berlan]

You don't have to use paste special, you only have to define the .value of the range. Try e.g.,

Please Login or Register  to view this content.

[chris01252]

Thanks berlan, that codes does work, is there anyway to incorporate a variable element to the date I am inserting as it won't always be the same date and won't always be copied from the same cell.

For example I want to copy the date from another sheet and then paste it, I was thinking of some sort of 'relative' macro i.e. copy the selected cell, then execute your code?

[berlan]

Define the output sheet name to make it more robust, so you can run it from another sheet.

Another example, where you can define the cell to copy the value from -- here the first cell in your selection:

Please Login or Register  to view this content.

Hope this helps. Otherwise, feel free to upload an example workbook so that I can have a closer look.

[chris01252]

Amazing, exactly what I was looking for, really appreciate the assistance.