Macro to copy from one cell to another.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2000: I want to copy the contents of an active cell #1, select another cell #2 and paste into that cell while retaining the original text in cell #2.

It will not copy the text in the new cell #1 and add it to the text in the new cell #2. Whether I select "Relative Reference" when recording or not, the function only inserts the final text from the original cell #2 used to record the macro. The code always looks like the following, "Relative Reference" or not:
ActiveCell.Select
ActiveCell.FormulaR1C1 = "(beige)" [here I highlighted and copied the contents]
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"Omni lav with pigtails, stripped and tinned leads (beige)" [here was supposed to execute a paste to cell #2]
[the contents of the line above gets inserted every time the macro is run]
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(3, 0).Range("A1").Select
End Sub
 
I have always found what you are trying to do (copying
the contents of one cell & adding them into another cell
to be pretty futile. Excel considers the value (or text
string) you are trying to paste into to be dead wood and
overwrites it accordingly.That is not to say it cannot be
done however...

This may not be the most elegant way to solve the problem
but here goes...

Instead of Copying the cell contents do the following:

Cell Contents
A1 Hi
B1 There

:::::::::::::::::::::::::::::::::

Sub PseudoConcatenate ()

Dim TextValue01, TextValue02 <<These will capture the
current values in the two cells you want to perform this
action on.>>

TextValue01=Range("A1").Value
TextValue02=Range("B1").Value

Range("B1").Select
Activecell.Value=TextValue01 & " " & TextValue02
<<Ensure a single space before & after each ampersand
when typing it in - you will get a Compile Error
otherwise>>

End Sub

:::::::::::::::::::::::::::::::::

As an alternative have you considered the Concatenate
Formula?

Cell Contents
A1 Hi
B1 There

=concatenate(A1," ",B1)

=A1&" "&B1

<<The space in the quotes will separate the two words.>>

Result: Hi There

This will do the same with out the need writing code and
having to run the macro every time you want this function
performed.

Good Luck!

Latro
-----Original Message-----
Excel 2000: I want to copy the contents of an active
Click to expand...
cell #1, select another cell #2 and paste into that cell
while retaining the original text in cell #2.
It will not copy the text in the new cell #1 and add it
Click to expand...
to the text in the new cell #2. Whether I
select "Relative Reference" when recording or not, the
function only inserts the final text from the original
cell #2 used to record the macro. The code always looks
like the following, "Relative Reference" or not:
ActiveCell.Select
ActiveCell.FormulaR1C1 = "(beige)" [here I
Click to expand...
highlighted and copied the contents]
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"Omni lav with pigtails, stripped and tinned
Click to expand...
leads (beige)" [here was supposed to execute a paste to
cell #2]
[the contents of the line above gets inserted every time the macro is run]
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(3, 0).Range("A1").Select
End Sub

.
Click to expand...
 
Charlie

To amend the text from the active cell to the text one cell above it then
clear the Active Cell contents.

Public Sub addtext()
Dim moretext As String
Set currentSelection = Application.ActiveCell
currentSelection.Name = "oldrange"
moretext = Range("oldrange").Value
ActiveCell.Offset(-1, 0).Value = ActiveCell _
.Offset(-1, 0).Value & moretext
Selection.ClearContents
End Sub

Work this into your code. Note: I copied the contents without selecting the
cell.

Gord Dibben Excel MVP
 
Back
Top