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
Selection.Copy

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

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
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

49 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

    Reply
    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:
      http://www.exceltrainingvideos.com/multiple-if/
      http://www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
      http://www.exceltrainingvideos.com/nested-loops-excel-vba/
      Hope this helps.

      Reply
      1. Aravind Kumar

        Hello sir,
        I have watched one of your video(https://www.youtube.com/watch?v=wgCqFcylPQY) 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.

        Reply
    2. Pascale

      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

      Else

      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)

      Else

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

      End If

      Next WS2
      Next WB2

      End If

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

      Next WS
      Next WB

      End Sub

      Reply
  2. Keren Christina

    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
    Sheets(“CTI”).Select
    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
    Sheets(“Sheet1”).Select
    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,
    Keren.

    Reply
  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”
    Worksheets(“Sheet2”).Select
    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

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

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

    Reply
  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
    BR

    Reply
  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

    Reply
  10. AMY

    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.

    Thanks,
    Amy

    Reply
  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,

    Regards,

    Lijith

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

    Reply
  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
    Selection.Copy
    Workbooks.Open Filename:=”C:\Users\imran\Documents\OBESTICALS\salesmasterbook.xls”
    Worksheets(“sheet1”).Select
    erow = ActiveSheet.Cells(Rows.Count, 0).End(xlUp).Offset(1, 0) = Row
    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = flase
    End If
    Next i
    End Sub

    Reply
  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

    Sheets(“consolidation”).Range(“A2:LK50”).Copy
    ActiveWorkbook.Close

    ‘ 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
    Loop
    End Sub

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

    Reply
  16. sanjay sakpal

    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…

    Reply
  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
    Selection.Copy

    Workbooks.Open Filename:=”C:\Users\sdinger\Documents\C & J\Learnd\MannysFile.xlsx”
    Worksheets(“Sheet1”).Select
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    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

    Reply
  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

    Worksheets(“Report”).Range(“A2:BB65536”).Clear

    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
    NextCode:
    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

    Do

    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

    Loop

    rep:

    If Len(AddressStr) Then

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

    Else:

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

    Worksheets(“Report”).Activate

    ResetSettings:

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

    End Sub

    Reply
  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
    Haribabu
    9948258382

    Reply
  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

    Reply
  21. Beverly

    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
    Selection.Copy

    Workbooks.Open Filename:=”C:\Users\Desktop\Sales.xlsx”
    Worksheets(“Sheet1”).Select
    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
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False
    End If

    Next i

    End Sub

    Reply
  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
    Else
    Cells(i, 8).Value = “”

    End If
    Next i

    End Sub

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

    Reply
  24. Pasupathi

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

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

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

    Reply
  27. Keyur Patel

    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”

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

    Reply
  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)?

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

    Reply
  31. Manop

    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?

    Manop

    Reply
  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
    JP

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

    Thanks

    Reply
  34. Siddhartha

    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

    Reply
  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
    for
    i = 2
    Sheets(“sheet1”).Range(“j:j”).Select
    if sheet1.raneg(j:j).cell.value= pending
    copy row to range a1
    Range(“J:J”).Select
    if cell.value = “approve” or “reject” than
    Range.Cells.Value.Copy
    Paste [Destination].Row(“j” & i)
    Next i
    End Sub
    =========================================================================
    Thanks in advance.
    Deepak

    Reply
  36. Joey

    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
    From:
    If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then

    To:
    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?

    Thanks

    Reply
  37. preethi karalapati

    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
    Selection.copy
    Workbooks.Open Filename:=”C:\Users\preet\Desktop\latest\preethi3.xlsx”
    Worksheets(“sheet1”).Select
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.PasteSpecial
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False
    End If

    Next i
    MsgBox (“Task completed”)
    End Sub

    Reply
  38. Josh

    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
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “855-584-8076”
    Rows(“1:246”).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “855-711-0011”
    Rows(“9:37”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A4”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “855-800-7711”
    Rows(“38:107”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A6”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-304-9181”
    Rows(“15:22”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A8”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-305-3955”
    Rows(“6:8”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A10”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-312-0780”
    Rows(“2:18”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A12”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-411-6370”
    Selection.RowHeight = 14.25
    Rows(“4:5”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A14”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-698-3102”
    Rows(“7:13”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A16”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-814-5041”
    Rows(“3:33”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Columns(“I:I”).Select
    Sheets(“Sheet1”).Select
    Range(“A18”).Select
    ActiveSheet.Paste
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    ActiveSheet.Range(“$A$1:$AS$65364”).AutoFilter Field:=9, Criteria1:= _
    “877-900-1546”
    Sheets(“Sheet1”).Select
    Range(“A20”).Select
    Sheets(“13497 glf-bl wchk-tx 65k 8-2 pr”).Select
    Rows(“20:24”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“Sheet1”).Select
    Range(“A20”).Select
    ActiveSheet.Paste
    Range(“I26”).Select
    End Sub

    Reply
  39. Pingback: Copying Multiple Rows One Below The Other From Different Sheets Based On Filter Codn. – windowsloadon.com

  40. Dianne Forman

    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?

    Reply
  41. Dianne Forman

    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
    Selection.Copy
    Worksheets(“2”).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
    Selection.Copy
    Worksheets(“3”).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
    Selection.Copy
    Worksheets(“4”).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

    lastline:
    Next i

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

    End Sub

    Reply

Leave a Reply

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