Getting Cell Reference With Input Box

Posted on the September 9th, 2008 under Excel VBA Function by Poer @ Excel VBA/Macro

We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel.

Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.

Sub CopyPaste()

Dim InputCells As Excel.Range
Dim OutputCells As Excel.Range

On Error Resume Next

'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(Prompt:="Block input cells/range", _
Title:="Copy Paste", Type:=8)

'Show input box to get where they want it paste
Set OutputCells = _
Application.InputBox(Prompt:="Select cell where you want paste it", _
Title:="Copy Paste", Type:=8)

'Copy range of input cells
InputCells.Copy

'Paste it into output cells reference
OutputCells.PasteSpecial (xlPasteAll)

End Sub

Run the excel macro, when the first input box show up, block range of cells that we want to copy, click OK, and when the second input box showed, choose destination cell and click OK, then the source cells will be copied into the destination cell.

Related Entries

External Resources

Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: Copy, Input Box, Paste

6 Responses to 'Getting Cell Reference With Input Box'

RSS

October 18, 2008 at 4:35 am
JP

The Copy Method accepts a Destination, if you declare InputCells and OutputCells as Range Objects, you can take advantage of the Intellisense. Also, the code will silently fail if no range is selected. Here’s an updated version with declarations and error handling:

Sub CopyPaste()

Dim InputCells As Excel.Range
Dim OutputCells As Excel.Range

On Error Resume Next
'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(prompt:="Block input cells/range", _
Title:="Copy Paste", Type:=8)
On Error GoTo 0

If InputCells Is Nothing Then GoTo ExitProc

'Show input box to get where they want it paste
Set OutputCells = _
Application.InputBox(prompt:="Select cell where you want paste it", _
Title:="Copy Paste", Type:=8)
On Error GoTo 0

If OutputCells Is Nothing Then GoTo ExitProc

'Copy range of input cells
InputCells.Copy OutputCells

ExitProc:
Set InputCells = Nothing
Set OutputCells = Nothing
End Sub

–JP

October 25, 2008 at 1:40 pm
admin

yaa you are right jp, if the input range object is nothing, then its will fail, thanks for your revision
November 7, 2008 at 9:59 am
James Quinn

Well done. I needed a way to grab user range. This showed me how. Preesh.
June 10, 2009 at 2:47 am
pankaj

how do I change it to paste values and formats?
June 14, 2009 at 11:17 pm
Excel VBA Macro

hi pankaj, if you use my vba code above, then it’s already set to paste all, both values and formats.
June 21, 2009 at 3:23 pm
Viviana Maphis

This macro has been very useful, how ever, I cant paste outside the workbook, how can I add a line to prompt for a file or just move to another file and paste?

Thank you