Copy Data to Another Excel WorkBook Based on Criteria Using VBA

Dear Sir,

We are really thankful to you for uploading such important videos on YouTube. We have a workbook called DayBook where we enter the daily transactions. We would like to extract all rows that contain the entry ‘sales’ and copy them to a ‘mastersales’ workbook. Also we would like to update our ‘mastersales’ workbook on a daily basis so that only ‘sales of today’ are posted to the ‘mastersales’ workbook. How can we achieve this?

Thanks & Regards

Jain & Co.

What Jain wants to do is the following:

  1. Identify data in a workbook sheet based on a text criterium and a date criterium or condition using a looping process
  2. Select the specific data which meets the two or multiple conditions
  3. Copy the identified data
  4. Open another workbook
  5. Find the first blank row in a specific worksheet in the workbook
  6. Paste the data in the identified blank or empty row – erow
  7. Save the workbook
  8. Close the workbook

The VBA code given below does the job quickly and easily by identifying the relevant rows and then transferring them to another relevant workbook:

Sub mySales()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then
Range(Cells(i, 1), Cells(i, 7)).Select

Workbooks.Open Filename:=”C:\Users\takyar\Documents\salesmaster-new.xlsx”
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
Application.CutCopyMode = False
End If

Next i
End Sub

Watch the training video below:

Download an Excel sample file for pratcice:

Further reading:
Excel VBA Programming Loops

42 thoughts on “Copy Data to Another Excel WorkBook Based on Criteria Using VBA

  1. Troy Geri

    I have a spreadsheet called project dashboard. I need to be able to suck in data from multiple spreadsheet s in a share point folder – all these source files have the same structure and tab nameof source data is the same.. The tricky bit is the target file where data needs to be consolidated… First based on projectid and asset id column in target, only if the unique combo does not exist should an insert be made into the target. Second if the project id does not exist at all in the target then the insert should start at the next blank row, if it does exist then it should insert a new blank row under the last row with that project id. Finally if a project id and asset id exist in the target but not in any source file then this means it has been removed from the source and so should now be deleted from the target, Happy to post sorce sheet samples and target master sheet to assist. Need to be able to have the macro invoked on the consolidated sheet on open and via a macro button within the sheet as well

    1. Dinesh Kumar Takyar Post author

      1. First check for 2 conditions using ‘IF’ with ‘AND’
      2. If projected does not exist – again use IF –> erow
      3. If project id and asset id exist in target but not in source then loop through the target and based on the values of project id and asset id, delete it
      These links might help:
      Hope this helps.

      1. Aravind Kumar

        Hello sir,
        I have watched one of your video( regarding Transfer Data from Multiple Workbooks into Master Workbook Automatically.
        information is very useful.
        this is like what I was searching for but with a small change. I have a question
        * I want data to be copied from columns instead of rows and with the date conditions.
        I have to obtain data from 40 work books, each employee having individual work named after them which is containing phone# they worked on today.
        they will save all numbers in the first column from A1 to A–(range depends on number of phone# worked on maximum is 120) and in B2 today’s date (i.e ctrl+;).
        Now what I want is, I want to create a Dailyhandoff work book in which I want to run a macro through which I should get
        1.—->>All the numbers should be copied in the format of,
        Name in first column, Phone# in second column like,
        –> if the 1st employee (workbook named after employee name) worked on 10 numbers, in my result workbook it should display as 10 times his/her name and all the 10 number he/she worked on.
        2.—->> I want sub total for the above employee1 – like 1stemployee count = 10
        3.—->> Now it should collect data from all the 40 sheets and paste it in a new workbook like in the above asked format (#1, #2 ) one by one.
        4.—->> And it should count all the sub total and display as Grand total = (sum of all sub totals).
        Please help me out.

  2. Keren Christina


    I would like to extract data from one sheet call(CTI) to another sheet call(sheet 1). i have used the below VBA but it does not work.

    Sub test2()

    RowDestination = 3
    LastRow = Cells(Cells.Rows.Count, “A”).End(xlUp).Row
    For RowA = 3 To LastRow
    If Cells(RowA, “N”) = “CFM Standard” And Cells(RowA, “N”) “CFM Standard” Or Cells(RowA, “Q”) “Standard” Then
    SONo = Cells(RowA, 6)

    End If
    Cells(RowDestination, “A”) = SONo

    RowDestination = RowDestination + 1
    Next RowA

    End Sub

    Could you help me with this. I can attach the excel to you if you want to. I need your help urgently.

    Appreciate for reply.

    Thanks & Regards,

  3. Omar

    Hi I have tried your code above for Jain & Co. and i keep getting an error in 2 places.

    the first place is the workbook file name – i like to use underscore in my file names so they automatically get sorted they way i need them, so the debug keeps asking me to change the underscore due to Syntax error

    the second issue is the part of the code that says – Worksheets(“Sheet2″).Select (which is the 2nd row below)

    Workbooks.Open Filename:=”C:\Users\Omar\Desktop\AZS_Pipeline_CLOSED_140919.xlsx”
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    when i run, it always shows me the – “). highlighted as if there is an issue with it

  4. JK Rao

    I am having 20 sheets first page having index & other sheets (already set print page) want to copy in new xls book only selected few sheets (selected on index page)

    I am having cell(j3) having selected sheet name with vlookup
    cell(j3) variable.

  5. Pingback: Web Based Excel Vba | Technology Documents

  6. Vitovschi Codrin

    I have a worksheet with some Tasks and which teams are assigned to do the task, I want to copy specific cells to a different worksheet based on the team. e.g. If it’s team A I want to copy the entire row to an excel file called Team A in a worksheet. I have 6 teams and 6 different files, how would I be able to do this?

  7. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

  8. Lucas

    Hello, i’ve two workbook, and i want to update one worsheet with the new value of the new worksheet.
    Whati i want to do is, when collumn 4 of the first Worksheet= Collumn 4 of the second worksheet
    (The value are not in order)
    Copy the value based on the combobox1 and combobox2 Based of the row header

    Here the part of my code whitch don’t work :

    Public Sub CommandButton1_Click()

    Dim lastRow As Integer, eRow As Integer

    i = 1
    m = 1
    p = 1
    j = 1

    lastRow = Entree1.Range(ComboBox1.Value & Rows.Count).End(xlUp).Row

    For i = ComboBox1.Value To lastRow

    If Entree1.Cells(p, 4).Value = Sortie2.Cells(m, 4).Value Then

    eRow = Sortie2.Range(ComboBox2.Value & Rows.Count).End(xlUp).Row

    For j = ComboBox2.Value To eRow

    i = j
    Next i
    Next j

    End If
    p = i + 1
    m = m + 1
    MsgBox (“Updated”)

    End Sub

    Any help would be appreciate

  9. najumudeen

    when i serch for a particular item ie sales how to get all data belongs to sales on another sheet
    using a search form

  10. AMY


    I appreciate your help in advance.

    i have a requirement, where i have to generate a worksheet which has data in tabular form which contains data i have provided in the parent workbook. and also i want to email the new worksheet which has been generated. because of privacy concerns i would not be able to share the sample data. Hope I have my query clear.


  11. Lijith

    I want to copy certain rows from different workbook to a master copy, but the criteria for the selection of row from the work books is based on the date entered on the left most cell of the rows . and all work books follow the uniform structure. It would be great if I could enter a date in the input box and then the macro copy the rows with that particular date in the left most column. Please find below the snapshot of the work book dat that needs to be copied.

    Thanking you in advance,



  12. ashishvb

    hi all,

    I am having table like below with large data in DATA.xlsx file

    col-A Col-B col-C col-D
    Area Task Report hours
    Project Assignment OO Report 3.0
    Project Assignment OO Report 3.0
    Project Assignment OO Report 3.0
    Total 9.0
    Monthly Enhancement AV Report 2.0
    Monthly Enhancement AV Report 4.0
    Project Assignment AV Report 3.0
    Total 9.0

    My requirement is to select the whole range of data from A1:D9
    and copy the sum hours to other excel sheet(report.xlsx) if the values in col-A is equal to “Project”.

    I had tried to use the above code but copies all the value instead of specific.
    Please some help me out for this solution.

  13. imran

    am trying this code as shows in ur video but not working can u help me
    ub mysales()
    Dim Lastrow As Integer, i As Integer, erow As Integer
    Lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

    For i = 2 To Lastrow

    If Cells(i, 1).Value = Date And Cells(i, 2).Value = “sales” Then
    Range(Cells(i, 1), Cells(i, 8)).Select
    Workbooks.Open Filename:=”C:\Users\imran\Documents\OBESTICALS\salesmasterbook.xls”
    erow = ActiveSheet.Cells(Rows.Count, 0).End(xlUp).Offset(1, 0) = Row
    ActiveSheet.Cells(erow, 1).Select
    Application.CutCopyMode = flase
    End If
    Next i
    End Sub

  14. Charlie Watson

    Dear Sir,

    I would like you to look at the code below which combines data from different workbooks.

    I need help in changing the pasting part of the code. It should be a pastespecial values instead of just plain paste. With the current code, the dates in UK format is converted to the American format.

    Many thanks in advance for your assistance.

    Charlie Watson

    Sub LoopThroughDirectory()
    ‘Set up the files to copy from

    Dim MyFile As String

    ‘ Set up the area where to connect the succeeding data (on the first empty row)

    Dim erow

    ‘ Set up the location of the files

    Dim Filepath As String
    Filepath = “A:\Decision Support\Diabetes\(4) Routine reporting\2015\Toujeo Activity Monitor\Test AV\”
    MyFile = Dir(Filepath)

    ‘ Pick up or copy from files with certain file name length and exclude the destination file

    Do While Len(MyFile) > 0
    If MyFile = “Consolidation Tool.xlsm” Then
    Exit Sub

    End If
    Workbooks.Open (Filepath & MyFile)

    ‘ Open each file and copy from a specific worksheet and cell range, then close book


    ‘ Paste the data in the first identified empty row of sheet 1 of this active file following the erow setting

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 323))
    MyFile = Dir
    End Sub

  15. Prarthna

    Hi, I have a requirement of picking the “project numbers” from one worksheet of multiple workbooks and paste them in another worksheet “drivers”of the same workbook from where the “project number” was picked up . I will then need to pull out these worksheets “Drivers” from the 100+ workboooks and add them in one workbooks in different sheets. I am able to do the 2nd part of extracing all the “driver” worksheets from the 100+ workbooks and pasting it in a single workbook. Could you help me on how to get the “project numbers” into the “driver” worksheet of each workbook ?

  16. sanjay sakpal

    I need a VBA Code to create a macro to get rid of tiresome work….

    I am using Office 2010.

    I have an folder name D:\Policy Data in which Policy Record.xlsx is File which contains 6 sheets.
    Out of these sheets, 1 sheet named “OUTPUT” has contain cell A1 : Branch Name & B1 : Today Date & I want to copy cell range from A2:AQ10456 to another workbook as values which must have name as Branch Name with date.xlsx i.e. specified in A1 and B1 of OUTPUT sheet.

    In this sheet I have used Array formulas to get desired on the basis of A1: Branch Name & B1: Today Date

    In manual practice I have to copy range cell into another workbook as values & save it as ‘branch name with Date’ for all 37 branches everyday.

    Can anyone help me to get out of this annoying work??
    Thanks a lot in advance…

  17. Seth

    I have the code in very similar as to what you have above, to suit my purpose, and when it gets to the ActiveSheet.Paste, I get an error
    Here is what I am using:
    Sub Send_Manny1()
    Dim LastRow As Integer, erow As Integer
    LastRow = Worksheets(“Records”).Range(“B” & Rows.Count).End(xlUp).Row

    Range(Cells(LastRow, 1), Cells(LastRow, 8)).Select

    Workbooks.Open Filename:=”C:\Users\sdinger\Documents\C & J\Learnd\MannysFile.xlsx”
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Cells(erow, 1).Select
    Application.CutCopyMode = False

    End Sub

    Can you help me figure out what I am doing wrong? It will not paste and I get an error code.
    **Except when I use F8 and manually run through the code, it works fine.
    Thank you for your help

  18. abdullah

    please help me with this , below code doing the this ,

    search one value in all workbook and its all worksheets are in one folder then copy the whole ROW in same work “Report” sheet, but when the copy function reach to row number 8 the found value copied over it I get only 7 ROW copied to my report sheet . I don’t know why .?

    Sub LoopinFolder()

    Dim FileName As String

    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim ws As Worksheet, Found As Range
    Dim myText As String, FirstAddress As String
    Dim AddressStr As String, foundNum As Integer

    Dim PickFolder As FileDialog


    myText = InputBox(“enter the string”)

    If myText = “” Then GoTo rep

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ‘Target Folder Path
    Set PickFolder = Application.FileDialog(msoFileDialogFolderPicker)

    With PickFolder
    .Title = “Select A Target Folder”
    .AllowMultiSelect = False
    If .Show -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & “\”
    End With

    ‘In Case of Cancel
    myPath = myPath
    If myPath = “” Then GoTo ResetSettings

    myExtension = “*.xls”

    myFile = Dir(myPath & myExtension)

    Do While myFile “”

    FileName = myPath & myFile

    Set wb = Workbooks.Open(FileName)

    For Each ws In wb.Worksheets
    With ws

    Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

    If Not Found Is Nothing Then
    FirstAddress = Found.Address


    foundNum = foundNum + 1
    AddressStr = AddressStr & “Found in file -> ” & wb.Name & ” ” & .Name & ” ” & Found.Address & ” ” & vbCrLf

    Set Found = .UsedRange.FindNext(Found)

    Found.EntireRow.Copy (ThisWorkbook.Sheets(“Report”).Range(“A65536”).End(xlUp).Offset(1, 0))

    Loop While Not Found Is Nothing And Found.Address FirstAddress

    End If

    End With

    Next ws

    ‘ Close Workbook

    wb.Close savechanges:=False

    ‘next file name
    myFile = Dir



    If Len(AddressStr) Then

    MsgBox “Found: “”” & myText & “”” ” & foundNum & ” times. ” & vbCr & vbCr & AddressStr, vbOKOnly, myText & ” found in these cells”


    MsgBox “Unable to find ” & myText & ” .”, vbExclamation
    End If



    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    End Sub

  19. Haribabu Yamala

    Hi Sir,

    I have a worksheet of 14 columns, need to filter column J with the filter values and copy all the worksheet into new work book,it keeps looping/repeating till last filter value. Can you please help me with VBA code.

    Thanks in advance

  20. Archana Pillai

    Dear Sir
    I have a worksheet which fills up values daily on runing macro selects a folder and exports this data
    col 1 Col2
    Test1 Pas
    Test2 Fail
    Test3 Pass
    So now if i save this worksgeet for today as 27may.xlsm and tomorrow as 27May.xlsm
    I want to create a new master workbook which will check that if sheet of that day(27may 28may) ran test1 test 2 test 3 then fill up it with test runned other wise put value missed
    27May 28May
    Test1 yes yes

    Test2 missed yes

    Test 3 Yes missed

  21. Beverly

    I found your post very useful.
    I need to copy data based on a filter criteria from a master book and paste into a several separate workbooks.

    File to copy the data from: Database.xlsm Tab Combined Data
    File to copy the data into: Sales.xlsx
    Note that data in both files have header in Row 1 and Data starts from Column E to Column AM

    Database file has 2 tabs – Combined Data and Only Sales. I have inserted a button in Tab Only Sales , which when clicked should select the data from the Tab Combined Data based on the filter (eg. Branch office), open the file Sales. xlsx, copy the data just below the header from Column E onwards. Whenever any new data is added to the master file and when the button is clicked, only the newly added data should be copied, leaving previous data in the Sales.xlsx file intact.

    I have used the following code but the code does not seem to work. I do not get any error messages so I assume the code syntax is correct but the rows / columns are not picking up correctly. Could you help me to get this right?

    Private Sub CommandButton1_Click()
    Dim LastRow As Integer, i As Integer, erow As Integer

    LastRow = Sheets(“Combined Data”).Range(“A” & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

    If Cells(i, 8) = “Tamworth” Then
    Range(Cells(i, 1), Cells(i, 35)).Select

    Workbooks.Open Filename:=”C:\Users\Desktop\Sales.xlsx”
    If LastRow < 2 Then
    LastRow = 2
    End If
    Worksheets("Sheet1").Rows("2:" & LastRow).ClearContents
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ActiveSheet.Cells(erow, 1).Select
    Application.CutCopyMode = False
    End If

    Next i

    End Sub

  22. Pablo Quiroga

    hello Excel’s VLOOKUP function is excellent when you want to find a value in a table based on a lookup value. But if your table includes your lookup value multiple times, you’ll find that VLOOKUP can’t do it.

    I have this code which works well but only take frist value :$ same that VLOOKUP. Can somebody help me to improve this code? Thanks

    Sub Match()

    Dim i As Long, LastRow As Long
    LastRow = Range(“A” & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow
    If Cells(i, 4).Value = Cells(i, 7).Value Then
    Cells(i, 8).Value = Cells(i, 2).Value
    Cells(i, 8).Value = “”

    End If
    Next i

    End Sub

  23. Rakesh

    Thank you Sir for uploading such informative material !
    I wanted your help in creating a macro which copies data from various workbooks with similar data and consolidate to another master workbook. However the selection should be based on a date range i.e, it should have a calendar drop down to select a starting and ending date range. Is it possible by inserting a combo box?

  24. Pasupathi

    I need your help on the following :

    I have the following data in one work sheet :
    E.No Name Designation Dept Grade Category
    10001 Raj Officer Finance M1 Executive
    12005 Ragu Asst Stores S2 Staff
    13008 Guru Operator Chemical Wa Workmen
    it goes on
    I want the details of employees of particular designation or Dept or category to be copied to another worksheet.
    I will give the criteria whether designation or dept or category.

    pl advise.

  25. Cristobal

    Hi, this post was very helpful for what I’m trying to do.

    However I hope if you could help me with this:

    I’m trying to do something that differs a little from the example in the post:
    1. Identify data in a workbook sheet based on a text criterium from a specific column using a looping process
    2. Select the specific data which meets a condition from data in a sheet
    3. Copy the identified data
    4. Open another sheet
    5. Find the first blank row in a specific worksheet
    6. Paste the data in the identified blank or empty row – erow
    Go to step 1 and repeat it with a different text criterium from a specific column. Then go to step 2, 3 and do 6 immediately after the data pasted before.

    Put in other words, in the line:

    If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then

    instead of looking for “Sales”, I need to go on and look for each category, which is determined by a string in a column, copy data and paste it in another worksheet. Then go and look for the next category represented by a different string and do the same process until all the categories have been copied to the other worksheet.

  26. Sam Zaid

    Dear Sir,

    I am seeking your kind help to accomplish an Excel file with VBA. I have very low knowledge and trying to learn through Website but this time i want to develop a new excel file, so need your help .Can you please provide me your email address??

  27. Keyur Patel


    Need your help to paste the details in same Sheet2 of same workbook instead of using below script:

    Workbooks.Open Filename:=”C:\Users\imran\Documents\OBESTICALS\salesmasterbook.xls”

  28. Chirag

    I am checking a condition with IF statement, IF Cells(i,j).Value=”TEXT”. This TEXT has blank spaces before and after and that’s why condition is not met anytime. Please suggest how to ignore blank spaces.

  29. Mike Moran

    Very useful video- thank you. MUCH shorter than what I had written.

    One question, though: why did you close the destination workbook each time before next i?

    Wouldn’t it run faster if you don’t keep closing the book every time (especially when there are thousand of rows)?

  30. sreenivas

    Hi Thanks for your videos .I have a scenario where I am having 25 sheets ina workbook but I have to copy only sheets from sheet 13 to sheet 22 . The data should come as a summary i.e after pasting sheet 13 data in seperate sheet the data from sheet 14 should come under sheet 13 data .Can you please help me to get this .Many thanks .

  31. Manop

    May I ask your help advise for the invoice generation from data table in another file then put that data to the invoice for
    1. Customer data
    2. Invoice data which are the list of items that we sold and those items will cause the number of rows changed so we have to adjust the number of rows when copy to the invoice template as well
    3. Need to print the invoice in PDF for those Original set and save to the same folder for each supplier and those copy set needs to to kept in 1 folder by the name of invoice number

    Would you please help advise how to do the in VBA excel?


  32. Juan Garcia

    Good Afternoon,
    I have the following data on Sheet1:
    Title Runs Total Runs Runs Left Status
    ASSAULT ON PRECINCT 13 3 3 0 Completed

    BACK TO THE FUTURE II 3 2 1 Incomplete

    BACK TO THE FUTURE III 3 4 -1 Extra Run

    And I have two different tabs. One is under the name “Completed” and the other one under the name “Extra Run”. What I want to do is copy all the “Completed” and “Extra Run” Titles to the respective tab and that once a title reaches the status of “completed” or “extra run” the information of that title will automatically be copied to the “Completed” or “Extra Run” tab. I have the following formula under status =IF(O10=0,”Completed”,IF(O10>0,”Incomplete”,IF(O10<0,"Extra Run"))) to know when a title is completed or have any extra run.

    Thank you for the help

  33. Sharon

    This is almost exactly what I need except for 2 things, this would be run on Fridays and would need to pull the rows based on a found word like sales but also for the current week beginning on Monday and endiing on the Friday it’s run. Second, I’d like to only pull certain columns not all and one in the series only if there is data in the field. Can this be done and how?


  34. Siddhartha

    I would like to ask you how to copy certain row of various sheet of a workbook in single sheet of the same workbook using vba or any other way

    For example If i have traffic count of 5 days of truck bus car micro Bike rickshaw and i want only total of each day and each category into one separate sheet

    what can i do
    thank you

  35. Deepak Gaba

    Hello Experts,

    Need some assistance to make a macro. Sheet1 is source data,
    In column “J” has 3 options : Approved, Rejected and Pending

    I want “Pending data” will copy in sheet2 in the same workbook.
    If I work on pending data and I make changes either Approved or Rejected . The same data will be reflected in Source File Sheet 1.

    One of my friends was trying to write the VBA code. Can you correct the code?
    Please find below the code:-
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim i
    i = 2
    if sheet1.raneg(j:j).cell.value= pending
    copy row to range a1
    if cell.value = “approve” or “reject” than
    Paste [Destination].Row(“j” & i)
    Next i
    End Sub
    Thanks in advance.


Leave a Reply

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