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

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

  1. 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. 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. 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. I am attempting to do something similar but the file names are not going to be a specific name, they will vary as will the data names. I have the code working across multiple worksheets and the last part of the code to color the boxes works across both the work books, but the primary most important part of the code only works on the initial workbook. Do you have any tips on why this might be or how I could get it to work across the second workbook?

      Sub Find_Part()
      Dim Part_Number As String
      Dim Found1 As Integer
      Dim Found2 As Boolean
      Dim Found3 As Boolean
      Dim Found4 As Boolean
      Dim Found5 As Boolean
      Dim Found6 As Boolean
      Dim Found7 As Boolean
      Dim Found8 As Boolean
      Dim Found9 As Boolean
      Dim Found10 As Boolean
      Dim Found11 As Boolean
      Dim Found12 As Boolean
      Dim EOS As String
      Dim EOSL As String
      Dim EOL As String
      Dim Replace As String
      Dim AddInfo As String
      Dim n As Long
      Dim m As Long
      Dim b As Long
      Dim LastRow As Long
      Dim WS As Worksheet
      Dim WS2 As Worksheet
      Dim WB As Workbook
      Dim WB2 As Workbook

      For Each WB In Workbooks

      With ActiveSheet
      LastRow = .Range(“A1″).SpecialCells(xlCellTypeLastCell).Row
      End With

      For Each WS In WB.Worksheets

      For m = 1 To LastRow

      Part_Number = WB.Sheets(WS.Name).Cells(m, 5).Value
      EOS = WB.Sheets(WS.Name).Cells(m, 20).Value
      EOSL = WB.Sheets(WS.Name).Cells(m, 21).Value
      EOL = WB.Sheets(WS.Name).Cells(m, 22).Value
      Replace = WB.Sheets(WS.Name).Cells(m, 23).Value
      AddInfo = WB.Sheets(WS.Name).Cells(m, 24).Value

      Found2 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 5).Value)
      Found4 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 20).Value)
      Found5 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 21).Value)
      Found6 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 22).Value)
      Found7 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 23).Value)
      Found8 = IsEmpty(WB.Sheets(WS.Name).Cells(m, 24).Value)

      If Found2 = True Then
      GoTo NextIndex


      For Each WB2 In Workbooks
      For Each WS2 In WB.Worksheets

      For n = 1 To LastRow
      Found1 = InStr(WB.Sheets(WS2.Name).Cells(n, 5).Value, Part_Number)

      Found3 = IsEmpty(WB.Sheets(WS2.Name).Cells(n, 20).Value)
      Found9 = IsEmpty(WB.Sheets(WS2.Name).Cells(n, 21).Value)
      Found10 = IsEmpty(WB.Sheets(WS2.Name).Cells(n, 22).Value)
      Found11 = IsEmpty(WB.Sheets(WS2.Name).Cells(n, 23).Value)
      Found12 = IsEmpty(WB.Sheets(WS2.Name).Cells(n, 24).Value)

      If Found3 = True And Found9 = True And Found10 = True And Found11 = True And Found12 = True Then

      If Found1 = 1 Then
      WB.Sheets(WS2.Name).Cells(n, 20).Value = EOS
      WB.Sheets(WS2.Name).Cells(n, 21).Value = EOSL
      WB.Sheets(WS2.Name).Cells(n, 22).Value = EOL
      WB.Sheets(WS2.Name).Cells(n, 23).Value = Replace
      WB.Sheets(WS2.Name).Cells(n, 24).Value = AddInfo

      End If
      End If
      Next n

      If Found4 = True And Found5 = True And Found6 = True And Found7 = True And Found8 = True Then

      WB.Sheets(WS.Name).Cells(m, 5).Interior.Color = RGB(255, 0, 255)


      WB.Sheets(WS.Name).Cells(m, 5).Interior.Color = RGB(255, 255, 255)

      End If

      Next WS2
      Next WB2

      End If

      Next m
      MsgBox (WB.Name & ” ” & WS.Name)

      Next WS
      Next WB

      End Sub

  2. Hi,

    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. 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. 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. 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?

  6. 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

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

  8. Hi,

    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.


  9. 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,



  10. 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.

  11. 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

    1. Doesn’t work for me either. Gives me a syntax error for Lastrow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row… I’m lost.

  12. 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

  13. 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 ?

  14. Hi
    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…

  15. 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

    1. I believe this is happening because the clipboard is being cleared when the new workbook is opened. I’ve been having the same problem with paste method error here and haven’t found a solution yet. Have you had any luck?

  16. 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

  17. 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

  18. 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

  19. Hello
    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

  20. 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

  21. 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?

  22. Sir
    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.

  23. 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.

  24. 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??

  25. Hello,

    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”

  26. 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.

  27. 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)?

  28. 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 .

  29. Hi
    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?


  30. 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

  31. 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?


  32. hello,
    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

  33. 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.

  34. Hi I am having a hard time with the if statement for this
    I simply want to change the validation from date and the word sales to a text string
    If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then

    If Cells(I,3)= “Tech Issue – Use for any tech issues affecting adherence” Then

    However I keep getting an error suggesting that the THEN should come after the word tech. Is there something I need to do different to have it look for the multi word string?


  35. Hi Sir,
    I have used this code for copy part for excel sheet and paste in master excel sheet. Ii have excel sheets in multiple folders. so I have used this macro in all required excel sheets to get data in master sheet. But my problem is when I run the macro in second sheet it is pasting the data in master sheet but the data which is already there in master sheet is changing its values automatically. The values which are changing are the values from formula. I think this is the reason for changing the column values. Can you please help me what extra code I have to write to not to change the column values in master sheet.
    Below is the code I have used

    Sub copy()
    Application.DisplayAlerts = False
    Dim i As Integer, erow As Integer, LastRow As Integer
    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
    For i = 16 To LastRow
    If Cells(i, “J”) = Date Then
    Range(Cells(i, 2), Cells(i, 12)).Select
    Workbooks.Open Filename:=”C:\Users\preet\Desktop\latest\preethi3.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
    MsgBox (“Task completed”)
    End Sub

  36. Hello Sir,
    I have been trying to figure how to complete a task of creating a macros for our mailing lists.
    Each mailing list has campaign phone numbers and I want to create a separate worksheet that contains two records per phone number.
    Now I was able to record a macro, although it isn’t generalized. Each file will have a unique name based on the campaign it is running for.
    Here is the recorded macros code. I’m hopping you can help me out on this.

    Sub TwoNumProof()

    ‘ TwoNumProof Macro
    ‘ 2 Records per Phone Number on a separate sheet

    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Selection.RowHeight = 14.25
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Application.CutCopyMode = False
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    Application.CutCopyMode = False
    End Sub

  37. I am trying to copy data from one worksheet 1 – to either worksheet 2, 3 or 4 based on the criteria found in column “B”. There are 3 different criteria scenarios, lets say criteria 2 which needs to copy to worksheet 2, criteria 3 which copies to worksheet 3 and the last, criteria 4 which copies to worksheet 4. I want it to copy the data and paste on the next empty row of the corresponding worksheet. I have copied your formula but used Elseif for the Criteria 3 & 4 but it only pulls over the first criteria which does go to the correct worksheet and does not go to the next row. Column “B” is a merged cell. I used pastespecial Paste: xlvaluesandnumberformats so it copies and pastes all of the merged cells. I think the problem is that the merge in column B which is rows 12:14, 15:17 etc would mean that rows 13. 14, 16 & 17 are all blank. How do I get it to skip those blank cells and go to the next row?

  38. Here is my current code:
    Sub MyCopy()

    Application.ScreenUpdating = False

    Dim LastRow As Integer, i As Integer, erow As Integer
    LastRow = ActiveSheet.range(“A” & Rows.Count).End(xlUp).Row

    For i = 11 To LastRow

    If cells(i, 2).Value > “” And cells(i, 2).Value = “2” Then
    range(cells(i, 1), cells(i, 59)).Select
    erow = Worksheets(“2”).cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Worksheets(“2”).cells(erow, 1).Select
    Selection.cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ElseIf cells(i, 2).Value > “” And cells(i, 2).Value = “3” Then
    range(cells(i, 1), cells(i, 59)).Select
    erow = Worksheets(“3”).cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Worksheets(“3”).cells(erow, 1).Select
    Selection.cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ElseIf cells(i, 2).Value > “” And cells(i, 2).Value = “4” Then
    range(cells(i, 1), cells(i, 59)).Select
    erow = Worksheets(“4”).cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Worksheets(“4”).cells(erow, 1).Select
    Selection.cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ElseIf cells(i, 2).Value = “” Then GoTo lastline

    End If

    Next i

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

    End Sub

  39. sir
    need help on vba
    need vba codes for moving the entire row to another workbook (not worksheet) where the word contains “send”. The word “send” may be in column D or E. Also word “send” will be like in column D as sender charges or sender chgs etc., Hence by the string “Send” the entire row should move to another workbook.
    thanks to help me with vba codes please

  40. Hello there,
    I wanted a program that could copy specific data from another workbook to specific cells in current workbook. Using a reference name for them.
    Say in a workbook there is a value or name (eg:$500) for name Axle.
    In my working notebook i need to just put a name and that value pops up in specific cell…
    Any help will be great

  41. Hi, Can any one help me with below query.

    I have to search a particular value in a cell where data exists, once that is determined i need to consolidate all those relevant rows of data in a table format and send a email out using out look.

  42. Hi,

    I’m having one dashboard. In that I need to update raw data from multiple worksheets from different workbook. I need to append day after day data in those sheets. As of now I’m doing manually copy paste day by day. Is there any macro code for the above concern.
    Please suggest

  43. One excel data is updating , I need to update in Master excel with updated data. Master Excel and raw data excel are having columns are not in same place. I want to get data from different excels and update in Master excel daily. Can you please help to come out of this

  44. One excel data is updating daily, I need to update in Master excel with updated data. Master Excel and raw data excel are not having columns in same place. I want to get data from different excels and update in Master excel daily. Can you please help to come out of this

  45. Hi Sir,

    Can you please help………..

    I am having one query. Excel contains Date Column -date- 7-Jan-2019 to 31-Jan-2019, and two tables Main heading with Leader name and below sub heading are there OpeingBal, Inflow,Completed,Pending like that for 2 leam leaders. Now I have to copy and 1st i have to paste 1st teamleader data in first row and then 2nd team leader data in second row (ex: teamLeader1– 1st-7-Jan-2019-Inflow,Completed,Pending, teamLeader2– 2nd-7-Jan-2019-Inflow,Completed,Pending , teamLeader1– 3rd row-8-Jan-2019-Inflow,Completed,Pending ) , I have to fill all the dates data.

  46. Thanks so much for your great teaching style. Do you have a video/instructions on doing the same thing to a worksheet in the same workbook? Also, What if I wanted to have two IF staments? for example using your example if I wanted to sort payroll also listed in column 2 into its own sheet. Thanks for the help!

  47. Hi Sir,
    i need to copy daily data into another excel sheet can u help me to code for that sometimes i may need to select 3 days data altogether please help me with that coding

  48. I changed the code slightly

    Sub mySales()
    Dim sPath As String
    Dim sString As String

    Dim LastRow As Integer, i As Integer, erow As Integer
    sPath = ActiveWorkbook.Path
    sString = Cells(2, 11).Value
    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False

    For i = 2 To LastRow
    If Cells(i, 11).Value = sString Then
    Range(Cells(i, 1), Cells(i, 11)).Select
    Workbooks.Open Filename:=sPath & “\” & “Archive.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
    Application.ScreenUpdating = True
    End Sub

  49. Dear sir, I need to export data from one workbook to another. Source file name should be written in particular cell and destination file name also be written in particular cell. data range is from E4:O (data range will be more or less but column range will be fixed. secondly if column F has value more than zero then data will be exported. please help me. Please ask me if you need more explanation what I need.

  50. Task one :-
    I need to combine the data into one master spreadsheet Tab from multiple tabs using Excel VBA macro code.

    Task two:-
    I need to split the data into multiple tabs from a master tab spreadsheet.
    Condition Heading Rows should copy and paste.
    Example in master tab spreadsheet in column A2 “Karnataka” and in column A15 “Karnataka” .

    My email address and contact number 9964149582.

  51. Hello Sir,

    I am trying to code below requirement but finding difficult.
    1. Have 2 workbooks (workbookA and WorkbookB) with same column names.
    2. Have a key column “EmpID” on column “A”
    3. Sort both WorkbookA and WorkbookB based on EmpID
    3. Need to traverse each row in WorkbookB and Paste the data in WorkbookA given EmpID is matching.
    4. IF EmpID is not matching, insert new Row based on Sorting or at the end of range in a new row.

    Please can you help me with this code.

  52. Hi I want to paste data with respect to my file name in my master file as per week no.
    Like if my master file contain XYZ name in particular cell,I want to open file which contains XYZ and copy particular matter from column range and paste as values.pls support

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.