Change the original formula to =Sheet1!$C$5.

Regards
Rowan

"Daniel M" wrote:

Rowan,

Great! this works better, and now that i have placed with the numbers and
understand it i can use it for different columns. Now the only problem i
have is that one of my cells has the formula =Sheet1!C5 in it and when it
copies the data it changes the formula to C5, C6, C7 etc...Is there a way to
only copy C5?

Thanks.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Hi Daniel

Assuming your data is in the first three columns, starting in Row 2 there
are a few ways to do this. If you simply want to copy the value (or
formula)
from cell B2 down to the end of you data then this should do it:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2))

That will replace anything that is already in Column B with whatever comes
from B2. If there are data (or formulas) in other cells in column B that
you
do not want to replace then this will fill in only the blank cells:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2)). _
SpecialCells(xlCellTypeBlanks)

Be aware that the second option will only work correctly if you have less
than 8193 non-contiguos ranges in Column B. If you have more than 8192
non-contiguous ranges then all data/formulae in column B will be replaced
by
B2.

Hope this helps
Rowan

"Daniel M" wrote:

Rowan,

Both solutions work as requested but the second one is not exactly what i
was looking for. Here is a better description...

I have data like this...
x y z
x z
x z

Now everywhere y is missing i need to copy y. So i need to copy the
contents
of y down to every open cell down to the row that data stops.

any help with this one?
dm.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another
sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically
changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a
"1"
in
an empty column but only where data exists in the rows. How can this
be
done?

Thanks.


dm.