paste into destination w/o overwriting existing

[SteveDB1]

Hi all.
We have a number of workbooks which list documents, in a form which we're
calling an abstract of title.
In our older workbooks, many of the abstracts list the sellers, and buyers
with 4 row, single column groups. all as unmerged cells.

With our newer formats, we now have these 4 row, 1 column groups merged into
a single cell. It just makes for a cleaner appearing layout.

This morning I tried recording a macro to select row 2, and paste its
contents into row 1 with already existing data. I then selected row 3, and
pasted that cell's contents into those of row 1, and did the same with row 4.

Once this was complete, I merged the 4 rows, and turned on word wrap.
The problem that I saw once I went in to edit the code was that it treated
the contents as an ActiveCell.FormulaR1C1.

Which of course gives the string of contents.

I then tried recording another macro to do the same thing by copy/paste, and
cut/paste. Once I activated the destination cell, to paste the contents of my
source cell, the cut/copy deactivated, and would not allow me to paste the
source contents to my destination cell.

As I need this to be more generic to cover all instances of this, I'm
curious as to what else I can use to accomplish this same goal, without over
writing the destination cell's existing contents.

How do I select a cell, cut its contents, and paste those contents into a
cell with already existing contents-- without overwriting those contents-- by
use of a macro?

The merging I can handle.
Thank you for your helps.

 

[Per Jessen]

Hi

Suppose you want to edit contents in A1

Range("A1").Value = Range("A1").Value & Range("B1").Value
Range("B1").ClearContents

Hopes it helps.

Regards,
Per

 

[SteveDB1]

Per,
Thank you.
My next question.
I've tried modifying what you stated to be more generic-- each row group
will change for each occurrence.
I tried the following, and it did not work.
the error that I get is that the object variable, or with block variable is
not set. And in setting a watch on each of my myRng_N variables, they are
"nothing."
When I placed the error elements in, they threw errors.
What have I missed here?
----------------------------------------------------------------------------------------

Dim myRng As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myRng3 As Range
Dim myRng4 As Range
Dim myRng5 As Range


Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select cell to move data to",
Type:=2).Cells(1)
On Error GoTo 0
'If myRng Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng1 = Nothing
On Error Resume Next
Set myRng1 = Application.InputBox(prompt:="Select first cell to move data
from.", Type:=2).Cells(1)
' On Error GoTo 0
'If myRng1 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng2 = Nothing
On Error Resume Next
Set myRng2 = Application.InputBox(prompt:="Select second cell to move data
from.", Type:=2).Cells(1)
' On Error GoTo 0
'If myRng2 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng3 = Nothing
On Error Resume Next
Set myRng3 = Application.InputBox(prompt:="Select third cell to move data
from.", Type:=2).Cells(1)
'On Error GoTo 0
'If myRng3 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If


Set myRng4 = Nothing
On Error Resume Next
Set myRng4 = Application.InputBox(prompt:="Select last cell to move data
from.", Type:=2).Cells(1)
On Error GoTo 0
'If myRng4 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng5 = Nothing
On Error Resume Next
Set myRng5 = Application.InputBox(prompt:="Select cells to merge.",
Type:=2).Cells(1)
On Error GoTo 0
'If myRng5 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

myRng.Value = myRng.Value & myRng1.Value & myRng3.Value & myRng4.Value

myRng2.ClearContents
myRng3.ClearContents
myRng4.ClearContents

myRng5.Select
With Selection
.MergeCells = True
End With

End Sub

 

[Per Jessen]

Hi

Set myRng = Application.InputBox(prompt:="Select cell to move data to",
Type:=8).Cells(1)

Change to Type:= 8 as shown above in all input statements.

Regards,
Per

 

[Per Jessen]

Hi

Try this. I think this is what you are trying to do.

Dim myRng ' As Range
Dim NewString As String

Sub steven()

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to concatenate",
Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.Count > 5 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If

For cell = 1 To myRng.Cells.Count
NewString = NewString & myRng.Cells(cell, 1).Value & " "
myRng.Cells(cell, 1).ClearContents
Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True

End Sub


Regards,
Per

 

[SteveDB1]

Per,
That does appear to be it. cool......
Thank you.
I had long thought concatenation is just placing the value of another cell
in to where the concatenate function is, yet still retaining its original
contents. Perhaps I'm just use to the one meaning of the term.