Getting Cell Reference With Input Box

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

  • Excel Extract Cell Comments

    Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary()Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer,...

  • Handy VBA To Calculate Person Age

    This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.Sub PersonAge() Dim iYears As Integer, iMonths...

  • Move Cursor To One Cell Below Last Row With Data

    Ever want to paste some data into new empty cell after the last cell with data in certain column? Then take a look at this simple macro.'first, select cell in the first row of that column, like A1, K1, etc Range("A1").Select'move to the last...

  • How To Open Excel File Using Macro

    Imagine this condition; We are in the middle of doing something using Excel VBA macro, then we want the macro to automatically show the File Open dialog box to make us (user) able to choose another Excel file to be opened by Excel. Got the picture? Below is vba procedure that do exactly like that, show...

  • How To Find the Last Row That Contain Data in Excel?

    UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: excel vba input box, input range vba, excel vba inputbox, input box vba, vba input range, vba inputbox, macro to open an excel file and paste data, microsoft excel input box macro, microsoft excel 2003 vba input dialog box, macro user prompt to select workbook to copy from, paste contents of an input box into a cell, macro input for sub, inputting ranges for macros, inputting a cell reference into another cell, inputbox vba excel

«
»