ExcelBanter - Choose which cell to paste data

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Choose which cell to paste data (https://www.excelbanter.com/excel-programming/442407-choose-cell-paste-data.html)

Choose which cell to paste data

Using VBA, I have copied the contents / comments / color etc.. of a
particular cell (single or merged), and wish to manually select (using
mouse pointer) which cell to paste the data.

So, I guess the VBA code should pause / stop until a new cell is
chosen and confirmed.
Is this posible?

My code so far is....

Private Sub CommandButton4_Click()
' MOVE data
Dim Answer As String
Dim MyNote As String
MyNote = "Move selected data?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation")
If Answer = vbNo Then
Exit Sub
End If
Selection.Copy
With ActiveCell
.Interior.ColorIndex = xlNone
.ClearContents
.ClearComments
.UnMerge
End With
MsgBox ("Please select new cell, where data will be copied to. ")

?????????????

ActiveSheet.Paste
End Sub

Thanks in advance.
Mik

Choose which cell to paste data

Type:=8 will allow you to use the mouse:

Sub movem()
Dim r1 As Range, r2 As Range
Set r1 = Selection
Set r2 = Application.InputBox(prompt:="pick destination", Type:=8)
r1.Copy r2
End Sub
--
Gary''s Student - gsnu201002

"Mik" wrote:

Using VBA, I have copied the contents / comments / color etc.. of a
particular cell (single or merged), and wish to manually select (using
mouse pointer) which cell to paste the data.

So, I guess the VBA code should pause / stop until a new cell is
chosen and confirmed.
Is this posible?

My code so far is....

Private Sub CommandButton4_Click()
' MOVE data
Dim Answer As String
Dim MyNote As String
MyNote = "Move selected data?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation")
If Answer = vbNo Then
Exit Sub
End If
Selection.Copy
With ActiveCell
.Interior.ColorIndex = xlNone
.ClearContents
.ClearComments
.UnMerge
End With
MsgBox ("Please select new cell, where data will be copied to. ")

?????????????

ActiveSheet.Paste
End Sub

Thanks in advance.
Mik
.

Choose which cell to paste data

On 8 May, 16:49, "Don Guillett" wrote:
Try this. Select the cell to copyfire the macroselect the paste cellmacro
finishes job

Sub copytoselectionSAS()
Selection.Copy Application.InputBox(Prompt:= _
*"Select your copy cell", Title:="SalesAid Software", Type:=8)
*With ActiveCell
* * .Interior.ColorIndex = xlNone
* * .ClearContents
* * .ClearComments
* * .UnMerge
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Mik" wrote in message

...

Using VBA, I have copied the contents / comments / color etc.. of a
particular cell (single or merged), and wish to manually select (using
mouse pointer) which cell to paste the data.

So, I guess the VBA code should pause / stop until a new cell is
chosen and confirmed.
Is this posible?

My code so far is....

Private Sub CommandButton4_Click()
' MOVE data
Dim Answer As String
Dim MyNote As String
MyNote = "Move selected data?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation")
If Answer = vbNo Then
* *Exit Sub
End If
Selection.Copy
With ActiveCell
* *.Interior.ColorIndex = xlNone
* *.ClearContents
* *.ClearComments
* *.UnMerge
End With
MsgBox ("Please select new cell, where data will be copied to. ")

?????????????

ActiveSheet.Paste
End Sub

Thanks in advance.
Mik- Hide quoted text -

- Show quoted text -

Thank you both for your reply.....

The cells that are being copied / moved can sometimes have a border
along one of it's edges (left or right, depending on it's location).
When i run your code, the borders also copy across to the new
location.

I don't want this to happen.
I have tried utilising the 'PasteSpecial
Paste:=xlPasteAllExceptBorders' routine, but i get errors.
Possibly entering it wrong.

Can you please assist?

Thanks
Mik

Choose which cell to paste data

Your best method would be to remove the borders on the destination cell
after the paste. As Gary's Student had set the destination range to a
variable r2, you can use that variable to remove the borders..

Set r2 = Application.InputBox(prompt:="pick destination", Type:=8)
r1.Copy r2
r2.Borders.LineStyle = xlNone

Even if there are no borders to remove, this will not throw an error.

Mike F

"Mik" wrote in message
...
On 8 May, 16:49, "Don Guillett" wrote:
Try this. Select the cell to copyfire the macroselect the paste
cellmacro
finishes job

Sub copytoselectionSAS()
Selection.Copy Application.InputBox(Prompt:= _
"Select your copy cell", Title:="SalesAid Software", Type:=8)
With ActiveCell
.Interior.ColorIndex = xlNone
.ClearContents
.ClearComments
.UnMerge
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Mik" wrote in message

...

Using VBA, I have copied the contents / comments / color etc.. of a
particular cell (single or merged), and wish to manually select (using
mouse pointer) which cell to paste the data.

So, I guess the VBA code should pause / stop until a new cell is
chosen and confirmed.
Is this posible?

My code so far is....

Private Sub CommandButton4_Click()
' MOVE data
Dim Answer As String
Dim MyNote As String
MyNote = "Move selected data?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation")
If Answer = vbNo Then
Exit Sub
End If
Selection.Copy
With ActiveCell
.Interior.ColorIndex = xlNone
.ClearContents
.ClearComments
.UnMerge
End With
MsgBox ("Please select new cell, where data will be copied to. ")

?????????????

ActiveSheet.Paste
End Sub

Thanks in advance.
Mik- Hide quoted text -

- Show quoted text -

Thank you both for your reply.....

The cells that are being copied / moved can sometimes have a border
along one of it's edges (left or right, depending on it's location).
When i run your code, the borders also copy across to the new
location.

I don't want this to happen.
I have tried utilising the 'PasteSpecial
Paste:=xlPasteAllExceptBorders' routine, but i get errors.
Possibly entering it wrong.

Can you please assist?

Thanks
Mik