Solved Moving cell data to another cell (not copy and paste)

Microsoft Excel for mac 2011 - macintosh
February 18, 2014 at 13:38:21
Specs: Mac OSX 10.8.4
There're a couple of things I'd like to do with a macro or some kind of key-in:

1. To move the content in an active cell to 6 cells (columns) to the right, remaining on the same row.
2. To move the content in an active cell to any other cell when selected (clicked).

Any help will be appreciated. TIA.

See More: Moving cell data to another cell (not copy and paste)

Report •

February 19, 2014 at 09:28:31
✔ Best Answer
Your first question is relatively simple compared to your second. This code will Cut the currently ActiveCell and Paste it 6 columns to the right in the same row.

Sub CopySixColunmsToRight()
    ActiveCell.Cut _
        Destination:=Cells(ActiveCell.Row, ActiveCell.Column + 6)
End Sub

Your second question is a bit more complicated.

You say you want the "ActiveCell" moved to a cell which the user will click. Well, guess what happens when that cell is Clicked? It instantly becomes the ActiveCell and all knowledge of the previously ActiveCell is lost.

While there is a way to capture the Click event and run VBA code when the Click happens, the code is not going to know which cell was Active before the next cell was clicked. In other words, let's say the ActiveCell is A1. When you click in B1 and the code fires, the VBA is going to pick up B1 as the ActiveCell and not know anything about A1.

Perhaps you need to rethink your second requirement. I don't think you can do it automatically. I think you are going to need some kind of InputBox asking the user to click to Source cell so the code can capture the address.

For example, with code below the user can DoubleClick the Destination cell to get the InputBox to appear, asking him to select the cell that he wants moved. That will capture the address of the "source cell" so that it can be moved when the user clicks OK.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo Cancelled
    Set srcCell = Application.InputBox( _
          "Please select the Data to be moved", Type:=8)
       Range(srcCell.Address).Cut Destination:=Range(ActiveCell.Address)
   Cancel = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 19, 2014 at 14:37:13
Thank you for your respond and the solution for the first part works very well. Thanks again.
Now as for the second part, let me try to explain a little bit of what I'm trying to do. Many many years ago when I was using the spreadsheet in Apple ClarisWork (iWork), I was able to select a cell, then press Command key (…I think, or one of those keys), and then click on a desired designation cell. And the content will be moved to the designation cell instantly. I used to use that a lot until I switched to Excel and found that it didn't have such a capability. I do move data from one corner of a spreadsheet to the opposite corner all the time. I find with such capability, I can move data a lot faster than either "copy and paste" or actually dragging the cell across the spreadsheet. I hope I've explained my intention a bit more clearly this time. Thank you.

Report •

February 20, 2014 at 19:01:40
You asked for a macro, so I offered one.

I know of no built in key-click sequence within Excel that will "move" (cut/paste) data from one cell to another.

This code, which is very similar to the automatic BeforeDoubleClick code above, will do something very close to what you are asking for.

I assigned the code to Ctrl-t. You would select the data you want to move, press Ctrl-t, select the cell you want to move the data to and Click OK.

Instead of pressing a single key like in iWorks, you need a 2 key sequence (Ctrl-t) and instead of just clicking the destination cell, you have to click the destination cell and then OK.

That's about all I can offer.

Sub MoveItHere()
' Keyboard Shortcut: Ctrl+t
    srcCell = ActiveCell.Address
 On Error GoTo Cancelled
    Set dstCell = Application.InputBox( _
          "Please Select The Destination Cell", Type:=8)
       Range(srcCell).Cut Destination:=Range(dstCell.Address)
   Cancel = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Related Solutions

February 20, 2014 at 22:19:23
You're absolutely awesome. You've given me tools that I've been looking for since I can't remember when. The second one doesn't do exactly what I was hoping for. But clicking an extra "OK" is much better than nothing at all. Thanks again for all your time and effort.

Report •

Ask Question