Copy Paste Data from one Excel Worksheet to another using an Inputbox

Earlier we had learnt 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 achieved 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

One thought on “Copy Paste Data from one Excel Worksheet to another using an Inputbox

  1. R. C. Tewari

    Pl help me,
    How to copy pest of specific range of column and pest in particular column range by the above VBA code.
    Regards
    TewariRC

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *