Excel VBA pop up form to move everything to new cell

Murray Brown
Murray Brown used Ask the Experts™

I have had a request to automate the moving of the value, formatting and comment from one cell
to another without dragging it. My customer wants to click in a cell and specify the address of the destination cell so that the cell values, formats and comments all move to that cell. What is the best way to do this, Should I use a right click with a form?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hardware Tester and Debugger
Hi there!

I have attached a general example. Instead of limiting it to one cell, I tried making it as useful as possible by being able to specify any cell or range you want to copy and paste into.

Sub CopyRange()

Dim CopiedRange As Range
Dim PasteRange As Range
On Error GoTo ErrorHandler
Set CopiedRange = Application.InputBox(prompt:="Select the Cell or Range to be copied.", _
Type:=8, Title:="Copy From Range")
Set PasteRange = Application.InputBox(prompt:="Select the destination Cell or Range.", _
Type:=8, Title:="Paste into Range")

With ActiveSheet
PasteRange.PasteSpecial xlPasteValuesAndNumberFormats
PasteRange.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

Resume Next
End Sub

Open in new window

If not, you can just change this line.
Set CopiedRange =ActiveCell

Open in new window

Group Finance Manager
Is this what you mean

Double click on the cell, select the next cell.
Murray BrownASP.net/VBA/VSTO Developer


Thank you very much
NoahHardware Tester and Debugger

You're welcome! Glad I was of help :)
Roy CoxGroup Finance Manager

Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial