Semi-Automatic Cut-Paste worksheet cells

[Big Lar]

I frequently need to move the contents of cells to blank cells. It becomes tedious to rightclick/cut, select new cell, rightclick/paste.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’d like to automate this task somehow and I’ve got half of a solution that works…<o></o>
<o> </o>
Select cell that I want to cut,<o></o>
Then, click CommandButton1 cuts the selected cell.<o></o>
<o> </o>
<o> </o>
Now I’m stuck…how to Doubleclick on the destination cell to paste.<o></o>
<o> </o>
Help with this or suggestions for a better method are appreciated.<o></o>

 

[Smitty]

How about posting the code you've got so far?

 

[GK039]

Hi there try the code below

Sub cutpaste()
Dim msg As String
Dim toRange As String
msg = InputBox("Select the cell to cut", "Cut - Paste")
toRange = InputBox("Select the cell to paste", "Cut - Paste")
Range(msg).Cut destination:=Range(toRange)
End Sub

Hope it helps

George

I frequently need to move the contents of cells to blank cells. It becomes tedious to rightclick/cut, select new cell, rightclick/paste.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’d like to automate this task somehow and I’ve got half of a solution that works…<o></o>
<o></o>
Select cell that I want to cut,<o></o>
Then, click CommandButton1 cuts the selected cell.<o></o>
<o></o>
<o></o>
Now I’m stuck…how to Doubleclick on the destination cell to paste.<o></o>
<o></o>
Help with this or suggestions for a better method are appreciated.<o></o>

 

[Big Lar]

How about posting the code you've got so far?

Here's the half that works...
Select cell/click CommandButton/select destination cell.

Private Sub CommandButton1_Click()
 
    ActiveCell.Select
    Selection.Cut
 
End Sub

 

[Big Lar]

Hi there try the code below

Sub cutpaste()
Dim msg As String
Dim toRange As String
msg = InputBox("Select the cell to cut", "Cut - Paste")
toRange = InputBox("Select the cell to paste", "Cut - Paste")
Range(msg).Cut destination:=Range(toRange)
End Sub

Hope it helps

George

George, that works nice but it takes longer than the old cut/paste method. And with my keyboard skills it'll take me forever to input the cut and paste ranges.

 

[Peter_SSs]

From your half-working code it appears that you would always be dealing with single cells. See if this is any use. It assumes that you would always be copying a cell containing data to a blank cell as that seems to be what you were describing in the first post.

Select both your Source and Target cells (use Ctrl + Click if they are not adjacent). It doesn't matter which order you select them so long as one is blank and the other is not. Then run the code (could be by clicking a button).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Cell()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> Range, t <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> bDisplayMessage <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Selection.Cells.Count = 2 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection.Cells<br>            <SPAN style="color:#00007F">If</SPAN> IsEmpty(c) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> t = c<br>            <SPAN style="color:#00007F">Else</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> s = c<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">If</SPAN> s <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> t <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            bDisplayMessage = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        bDisplayMessage = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> bDisplayMessage <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "You need to select a single (occupied) Source cell" _<br>            & vbLf & "and a single (empty) Target cell"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        s.Cut Destination:=t<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Big Lar]

Peter,

That's "Mickey Mouse".
Thanks for the code.