# Macro to Copy Data from One Workbook to Another Based on Criteria

Suppose we have two workbooks called Workbook1 and Workbook2. In Workbook1Â we have the Names, Types, and Prices of some books in a bookshop.

In this article, we’ll develop and run a Macro to copy data from Workbook1 to Workbook2 based on both a single criterion and multiple criteria.

### Method 1 – Copy Data Based on a Single Criteria

Letâ€™s copy the Names and Prices of the books with prices greater than \$20 from Workbook1 to Workbook2.

• Enter the following VBA code in a new module window and save it:
``````Sub Copy_Data_Based_on_Single_Criteria()

Column_Numbers = InputBox("Enter the Column Numbers of Your Selected Range to Copy [Separated by Commas]: ")

Dim Columns() As String
Columns = Split(Column_Numbers, ",")

Workbook = InputBox("Enter the Name of the Destination Workbook: ")

Sheet = InputBox("Enter the Name of the Worksheet: ")

Criteria_Column = Int(InputBox("Enter the Number of the Column with the Criteria: "))

Criteria = Int(InputBox("Enter the Criteria: " + vbNewLine + "Enter 1 for Greater than a Value. " + vbNewLine + "Enter 2 for Greater than or Equal to a Value. " + vbNewLine + "Enter 3 for Less than a Value. " + vbNewLine + "Enter 4 for Less than or Equal to a Value. " + vbNewLine + "Enter 5 for Equal to a Value. " + vbNewLine + "Enter 6 for Not Equal to a Value. " + vbNewLine + "Enter 7 for a Partial Match. "))

Value = InputBox("Enter the Value to Compare: ")

Condition = 0
Row = 1
Column = 1

For i = 1 To Selection.Rows.Count
Â Â Â Â If Criteria = 1 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) > Int(Value) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 2 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) >= Int(Value) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 3 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) < Int(Value) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 4 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) <= Int(Value) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 5 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) = Value Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 6 Then
Â Â Â Â Â Â Â Â If Selection.Cells(i, Criteria_Column) <> Value Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â ElseIf Criteria = 7 Then
Â Â Â Â Â Â Â Â If InStr(1, Selection.Cells(i, Criteria_Column), Value) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â End If
Â Â Â Â If Condition = 1 Then
Â Â Â Â Â Â Â Â For j = 0 To UBound(Columns)
Â Â Â Â Â Â Â Â Â Â Â Â Workbooks(Workbook).Sheets(Sheet).Range(Selection.Cells(Row, Column).Address).Value = Selection.Cells(i, Int(Columns(j)))
Â Â Â Â Â Â Â Â Â Â Â Â Column = Column + 1
Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Â Row = Row + 1
Â Â Â Â Â Â Â Â Column = 1
Â Â Â Â End If
Â Â Â Â Condition = 0
Next i

End Sub``````

Note: This code creates a Macro called Copy_Data_Based_on_Single_Criteria.

Output:Â

• Select range B4:D13 from Workbook1 and run this Macro.

Youâ€™ll get a few Input boxes.

The first Input Box will ask you to enter the numbers of the columns of the data set that you want to copy (separated by commas).

• Since we want to copy the Book Names and Prices, enter 1,3.

• Click OK.

The second Input Box will ask you the name of the Destination Workbook.

• Since we want to copy data to Workbook2, enter Workbook2.

The third Input Box will ask you the worksheet name of the destination workbook where you want to copy data.

• Since we want to copy data to Sheet1 of Workbook2, enterÂ Sheet1.

The fourth Input Box will ask you to enter the number of the column with the criteria.

• Since our criterion is price greater than \$20, which lies in column 3 of the selected data set, enter 3.

The fifth Input Box will ask you to enter the criterion.

Enter 1 if the criterion is greater than a value.

2 if is greater than or equal to a value.

3 if it is less than a value.

4 for less than or equal to a value.

5 for equal to a value.

6 for not equal to a value.

And finally, 7 for a partial match.

• Since our criterion is prices greater than \$20, enter 1.

The sixth and final Input Box will ask you to enter the value to compare.

• Since we want books with prices greater than \$20, enter 20.

• Click OK.

The names and prices of the books with prices greater than \$20 are copied from Workbook1 to Sheet1 of Workbook2.

### Method 2 – Copy Data Based on Multiple Criteria (Both AND and OR Types)

Letâ€™s copy the names, types, and prices of the books which are either novels or have prices greater than \$20.

Use the following VBA Code for this purpose:

``````Sub Copy_Data_Based_on_Multiple_Criteria()

Column_Numbers = InputBox("Enter the Column Numbers of Your Selected Range to Copy [Separated by Commas]: ")

Dim Columns() As String
Columns = Split(Column_Numbers, ",")

Workbook = InputBox("Enter the Name of the Destination Workbook: ")

Sheet = InputBox("Enter the Name of the Worksheet: ")

Criteria_Column_Numbers = InputBox("Enter the Numbers of the Columns with the Criteria [Separated by Commas]: ")

Dim Criteria_Columns() As String
Criteria_Columns = Split(Criteria_Column_Numbers, ",")

Multiple_Criteria = InputBox("Enter the Criteria: " + vbNewLine + "Enter 1 for Greater than a Value. " + vbNewLine + "Enter 2 for Greater than or Equal to a Value. " + vbNewLine + "Enter 3 for Less than a Value. " + vbNewLine + "Enter 4 for Less than or Equal to a Value. " + vbNewLine + "Enter 5 for Equal to a Value. " + vbNewLine + "Enter 6 for Not Equal to a Value. " + vbNewLine + "Enter 7 for a Partial Match. ")

Dim Criteria() As String
Criteria = Split(Multiple_Criteria, ",")

Criteria_Type = Int(InputBox("Enter 1 for OR Type Criteria: " + vbNewLine + "OR" + vbNewLine + "Enter 2 for AND Type Criteria: "))
Compare_Values = InputBox("Enter the Values to Compare: ")

Dim Values() As String
Values = Split(Compare_Values, ",")

Condition = 0
Conditions = 0
Row = 1
Column = 1

For i = 1 To Selection.Rows.Count
Â Â Â Â For j = 0 To UBound(Criteria_Columns)
Â Â Â Â Â Â Â Â If Int(Criteria(j)) = 1 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) > Int(Values(j)) Then
Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 2 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) >= Int(Values(j)) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 3 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) < Int(Values(j)) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 4 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) <= Int(Values(j)) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 5 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) = Values(j) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 6 Then
Â Â Â Â Â Â Â Â Â Â Â Â If Selection.Cells(i, Int(Criteria_Columns(j))) <> Values(j) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â ElseIf Int(Criteria(j)) = 7 Then
Â Â Â Â Â Â Â Â Â Â Â Â If InStr(1, Selection.Cells(i, Criteria_Columns(j)), Values(j)) Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Condition = Condition + 1
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â End If
Â Â Â Â Next j
Â Â Â Â If Criteria_Type = 1 Then
Â Â Â Â Â Â Â Â If Condition >= 1 Then
Â Â Â Â Â Â Â Â Â Â Â Â Conditions = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â Else
Â Â Â Â Â Â Â Â If Condition = UBound(Criteria) + 1 Then
Â Â Â Â Â Â Â Â Â Â Â Â Conditions = 1
Â Â Â Â Â Â Â Â End If
Â Â Â Â End If
Â Â Â Â Condition = 0
Â Â Â Â If Conditions = 1 Then
Â Â Â Â Â Â Â Â For j = 0 To UBound(Columns)
Â Â Â Â Â Â Â Â Â Â Â Â Workbooks(Workbook).Sheets(Sheet).Range(Selection.Cells(Row, Column).Address).Value = Selection.Cells(i, Int(Columns(j)))
Â Â Â Â Â Â Â Â Â Â Â Â Column = Column + 1
Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Â Row = Row + 1
Â Â Â Â Â Â Â Â Column = 1
Â Â Â Â End If
Â Â Â Â Conditions = 0
Next i

End Sub``````

Note: This code creates a Macro called Copy_Data_Based_on_Multiple_Criteria.

Output:Â

• Select theÂ range B4:D13 from Workbook1 and run the Macro.

Youâ€™ll get a few Input boxes.

The first Input Box will ask you to enter the numbers of the columns of the data set that you want to copy (separated by commas).

• Since we want to copy the Book Names, Types, and Prices, enter 1,2,3.

• Click OK.

The second Input Box will ask you the name of the Destination Workbook:

• Since we want to copy data to Workbook2, enter Workbook2.

The third Input Box will ask you the worksheet name of the destination workbook where you want to copy data.

• Since we want to copy data to Sheet2 of Workbook2, enter Sheet2.

The fourth Input Box will ask you to enter the numbers of the columns with the criteria.

• Since our criteria are book type novel and price greater than \$20, which lie in columns 2 and 3 of the selected data set respectively, enter 2,3.

The fifth Input Box will ask you to enter the criteria.

Enter 1 for greater than a value.

2 for greater than or equal to a value.

3 for less than a value.

4 for less than or equal to a value.

5 for equal to a value.

6 for not equal to a value.

And finally, 7 for a partial match.

• Since our criteria are book type equal to Novel and price greater than \$20, enter 5,1 (Equal to, Greater than).

The sixth Input Box will ask for AND Type or OR Type criteria.

Enter 1 for an OR Type criteria.

2 for AND Type criteria.

• Enter 1.

The final Input Box will ask you to enter the values to compare.

• Since we want the books with types of Novel or prices greater than \$20, enter Novel,20.

• Click OK.

The names, types, and prices of the books with book type Novel or prices greater than \$20 are copied from Workbook1 to Sheet2 of Workbook2.

Read More:Â Excel VBA: Copy Range to Another Workbook

## Things to Remember

• The source workbook and the destination workbook must be kept in the same folder on your computer. Otherwise, the code wonâ€™t work.
• While copying based on multiple criteria, you can have all the criteria from the same column. For example, you can copy the books with prices greater than \$20 and less than \$30. In that case, enter 3,3 in the fourthÂ Input Box.
• Here we’ve used just 2 criteria. However, you can use this macro with as many criteria as you like.
• The equal to, not equal to, and partial match criteria are case-sensitive.

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

1. Nice job,
It works fine until the line below, where it stops and show error 9: subscript out of range.

• Thank you Ana. It’s difficult to give any suggestions without having a glance at the workbook. Would you please kindly share your workbook with me?

2. how to copy data as values from selected worksheets to another selected workbook by simple vba

• Hi NARASIMHAN S,
You can copy data as values from the selected worksheets to another workbook using the VBA code. Here, Workbook1 is our source and Workbook2 is our destination Excel file. We want to copy data from Sheet1 of Workbook1. Use the below code on Workbook1.

VBA Macro to Copy Data of a Sheet into Another Workbook:

Sub Copy_Sheet_Another_Workbook_1()
Sheets(“Sheet1”).Copy Before:=Workbooks(“Workbook2.xlsm”).Sheets(1)
End Sub

Code Breakdown

Sheets(“Sheet1”).Copy Before:=Workbooks(“Workbook2.xlsm”).Sheets(1)
This will copy Sheet1 of the active workbook (Workbook1) into Workbook2 before 1st sheet. If you want to copy after 1st sheet of Workbook2, use After instead of Before in the VBA code.

One thing needs to keep in mind, that is both workbooks must be kept open in this case. The destination workbook name is mentioned in the VBA code. But if the source workbook is closed, you can use the VBA code below.

VBA Macro to Copy Data of a Closed Sheet into Another Workbook:

Sub Copy_Sheet_Another_Workbook_2()
Application.ScreenUpdating = False
Set sourcedBook = Workbooks.Open(“C:\Users\alokp\OneDrive\Desktop\Workbook1.xlsm”)
sourcedBook.Sheets(“Sheet1”).Copy Before:=ThisWorkbook.Sheets(1)
sourcedBook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

We applied this VBA code to Workbook2 and Workbook1 is closed. We inserted the location of Workbook1 and mentioned the sheet name in the VBA code. In both cases, we need to mention the source sheet name in the VBA code.

Advanced Excel Exercises with Solutions PDF