Copy Paste Data from one Excel Worksheet to
another using an Inputbox
Earlier we had learn how to copy paste data from one Excel worksheet to another using a macro. Although this macro is useful, the criteria is actually 'hard-wired'. That is, we need to specify the data we wish to copy inside the macro itself. Wouldn't it be more useful if we could interactively input the criteria for extracting the data to another Excel worksheet? This can be achived by using an inputbox in Excel VBA.
The code for the Excel macro is given below:
Sub copy_paste_data_from_one_sheet_to_another()
'Let's start at row 2. Row 1 has headers
x = 2
Dim myName As String
myName = Application.InputBox("Enter a name")
'Worksheets("Sheet1").Activate
'Start the loop
Do While Cells(x, 1) <> ""
'Look for name
If Cells(x, 1) = myName Then
'copy the row if it contains 'myName'
Worksheets("Sheet1").Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
Worksheets("Sheet2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
'go to sheet1 again and actvate it
Worksheets("Sheet1").Activate
'Loop through the other rows with data
x = x + 1
Loop
End Sub
Watch the Excel training video below to see the code in action:
While copying and pasting data from one worksheet to another Excel worksheet it can happen that you get this message:
Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?
Of course, you can continue but if the copied data is to be transferred thousand times you'll have to click thousand times!
You can avoid this by adding a line of code before the macro code line that pastes the data:
Application.DisplayAlerts = False
Also set the above line to 'true' in the code line after 'paste'.
In our example the position of the could would be:
Sub copy_paste_data_from_one_sheet_to_another()
'Let's start at row 2. Row 1 has headers
x = 2
Dim myName As String
myName = Application.InputBox("Enter a name")
'Worksheets("Sheet1").Activate
'Start the loop
Do While Cells(x, 1) <> ""
'Look for name
If Cells(x, 1) = myName Then
'copy the row if it contains 'myName'
Worksheets("Sheet1").Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
Worksheets("Sheet2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data here
Application.DisplayAlerts = False
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
Application.DisplayAlerts = True
End If
'go to sheet1 again and actvate it
Worksheets("Sheet1").Activate
'Loop through the other rows with data
x = x + 1
Loop
End Sub
More Tips (Solutions):
How do I view and edit Microsoft Excel files on an iPad?
Inventory Soluton
How to rename and color worksheet tabs
Copy Worksheet Quickly
Criteria Range
Animations in Microsoft Excel
Create two charts at once
How to avoid errors while working in Excel
How to benefit from Microsoft Excel Templates
How to use Microsoft Excel 2007 with Word 2007 - create mail merge labels
Landscape Oriented Worksheet Template
How to-make a worksheet fit a printed page
How to make your charts more impressive
Perpetual Calendar from 1900 to 9999
How to create your own custom add-in
How to clear conditional formattng in Excel worksheets
How to speed up data entry of decimal numbers
How to change the color of the gridlines of an Excel worksheet or hide them
How to use Autosum in Multiple Worksheet Cells Quickly
How to have your free personal assistant in Mcrosoft Excel who reads out the data to you
How to use the status bar in Microsoft Excel to do a quick data analysis
How to calculate equal monthly payments or instalments using a mathematical equation
Data Forms to Enter and Edit Data
View 2 worksheets in same workbook side by side
Accessing a specific worksheet in large workbooks with multiple worksheets
Referencing a cell in another worksheet
How to perform a what-if analysis using a scrollbar form control
An interesting payroll solution
Future Value Solution in Microsoft Excel
Another Interesting Conditional Formatting Solution
DSUM and Array Formulas for addition solutions
IF Function Question
How to use command buttons on a splash screen in MS-Excel
Copy Paste Data from one Excel Worksheet to Another
How to separate comma separated values (csv) in a worksheet cell in Microsoft Excel into rows or columns
Another Interesting Solver solution in Microsoft Excel
Find duplicates in two different Excel worksheets using a macro
How to count data based on multiple criteria - countifs function
How to convert 6/5/2011 into Sunday, 6/5/2011
User Form to perform calculations
An interesting solution using conditional formatting in Microsoft Excel 2007
Show/Hide Controls on a User-Form using a Checkbox
Populate Listbox and Textboxes with Excel data using VBA and Vlookup
Protecting Specific Worksheets in an Excel Workbook using VBA
How to calculate the difference in hours between two date-time values
How do I match data from 2 worksheets and highlight the differences using MS-Excel VBA?
How to focus on a specific control (TextBox1) on a user-form in MS-Excel
Interesting sumproduct solution
COMPARE DATA LISTS USING VLOOKUP AND ISNA FUNCTIONS
VLOOKUP SOLUTION USING LIST IN MS EXCEL
How to generate random numbers in MS Excel
Lottery game in Microsoft Excel
Nested If Function to Determine Project Complexity
Power Billing Solution in Microsoft Excel
Solver Excel - An easy to understand example
Another Interest Calculation Solution
Interesting Microsoft Excel Formula Based on Date and Row Functions
How to subtract dates in Microsoft Excel
Automating rounding of decimal numbers in MS-Excel
How to Calculate Profit Loss of security transactions in MS Excel
How to print comments in Microsoft Excel
How to Display Results in a Specific Way in Excel
How to Filter or Hide Rows & Display Excel Data in Excel Using A Macro
Automate Chart Creation Using Excel Macros (VBA)
Add Text To Displayed Excel Numerical Value
How to develop an advanced function or formula in MS-Excel
Apply Autofilter Across Multiple Excel Worksheets
Inventory Solution in Microsoft Excel
How to open a password protected Excel worksheet whose password has been lost
Hyperlinks and VBA Code to Navigate worksheets in Large Workbooks
Count Total Between Two Dates Using COUNTIFS Function
Dice Game in MS-Excel
Where is my autofill handle?
Payment Calculations based on Timestamps
How to convert an Excel File into a Text File
How to consolidate data from multiple workbooks or files
How to display the value of a number in words in Excel