Copy data into a table (but not the formula)

[Apollo]

Hi
First of all I apologise if this has been asked before.

What I want to know is it possible, and if so how does one do it, to d
the following ?

Copy data from a cell into another cell in a table but without having
formula in the destination cell , e.g

Say $b1 contains the name Fred and I want to place Fred into the $c
cell but I don't want there to be a formula in $c5 asking for it tha
will update the contents of $c5, if I change the value held in $b5, fo
instance, I put Fred in $b1 and Fred appears in $c5, but then I pu
another name, say Bert into $b1 and it goes to $c6, but the contents o
$c5 remains Fred, etc, etc

Any help greatly appreciate

 

[Ron de Bruin]

Hi Apollo

If I understand you correct?

With a header in C4 you can use this macro to copy the Value of B1
each time in the C column(starting in C5)
Every time you run the macro it will copy the value of B1 below the last cell
with data in column C


Sub copytest()
Dim sourceRange As Range
Dim destRange As Range
Set sourceRange = Range("b1")
Set destRange = Range("C" & Rows.Count).End(xlUp).Offset( _
1, 0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
destRange.Value = sourceRange.Value
End Sub

 

[Apollo]

Ron
Thanks, this works a treat

 

[Guest]

I understood the question and was excited for the answer. Your answer however was greek to me...macro


----- Ron de Bruin wrote: ----

Hi Apoll

If I understand you correct

With a header in C4 you can use this macro to copy the Value of B
each time in the C column(starting in C5
Every time you run the macro it will copy the value of B1 below the last cel
with data in column


Sub copytest(
Dim sourceRange As Rang
Dim destRange As Rang
Set sourceRange = Range("b1"
Set destRange = Range("C" & Rows.Count).End(xlUp).Offset(
1, 0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count
destRange.Value = sourceRange.Valu
End Su

 

[Ron de Bruin]

Start here

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA