Excel VBA pop up form to move everything to new cell

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hardware Tester and Debugger
Commented:
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.
Example.xlsm


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
CopiedRange.Copy
PasteRange.Parent.Activate
PasteRange.PasteSpecial xlPasteValuesAndNumberFormats
PasteRange.PasteSpecial xlPasteFormats
CopiedRange.ClearContents
Application.CutCopyMode = False
End With

ErrorHandler:
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
Commented:
Is this what you mean

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

Author

Commented:
Thank you very much
NoahHardware Tester and Debugger

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

Commented:
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