EAF#78 - Use an Excel VBA Loop to search a datatable and copy selected rows to another sheet

Поділитися
Вставка
  • Опубліковано 9 січ 2025

КОМЕНТАРІ • 66

  • @russiannpcbot6408
    @russiannpcbot6408 8 місяців тому

    You just saved my ass. I was looking for a way to do just this for a project at work. Getting anything approved for use by management is a right bitch. It gets tied up in red tape and BS. This completes a workbook project I've been making in my free time to make my work easier. My Workbook pulls data from an Excel workbook released by analysts then fills in pdfs using the Send.Key command with judicious use of tabbing and shortcut controls. It then fills two other Excel reports and prints pdf copies. All of this gets saved to the correct file location with the correct naming conventions applied. It does all of this without leaving a trace it was used. I've cut an hour of work for each case down to 15-20 minutes. This bit of code should move it closer to the 15 minute amount.

  • @familiapipajose2196
    @familiapipajose2196 2 роки тому

    Man! Thank you very much… you saved thousands of manhours from my daily workflow! Please keep it up!

  • @gillesdhoker2719
    @gillesdhoker2719 6 років тому

    you explained all the processes... very helpfull for a beginner in VBA. Thank you!

  • @sajalmajhi3656
    @sajalmajhi3656 7 років тому

    A lot of thanks. I make vba project as inventory management system. I tried lot of time to delete some data on same invoice number. Using your code it solved. Thanks

  • @leosanders4155
    @leosanders4155 3 роки тому +1

    wondering if there is video for code to loop through a list of names, find the matching names, and then pasting the entire row matching the criteria in different excel sheets. So if you have two names, John Doe and Michael Smith, it will find John Doe and put the data in sheet 2, eg. and then loop through a list where the next name is Michael Smith and then it finds that data and puts it in sheet3. thanks.

  • @petertaylor6484
    @petertaylor6484 7 років тому +2

    Hello, this is a great video and how it returns records dynamically is fantastic. But I was wondering if at the bottom of the last record (where ever that is) to then sum or total a column of numbers or statistics,. That also sum dynamically dependent on the number of records retrieved. Thanks a lot. Regards Peter Taylor

  • @armotxa124
    @armotxa124 4 роки тому

    Thank you
    ExcelTricksforSports. its works

  • @tronway5500
    @tronway5500 3 роки тому +1

    How can we make this code search multiple sheets.

  • @peteronio
    @peteronio Рік тому

    Hi, thanks for this great video. I made an exact copy but instead of a string i use an integer to search for a year. Although the declarations are good the If statement never results in an equal Year! Is there any solution for? Regards, Peter

  • @CarrisaMayVelasco
    @CarrisaMayVelasco Рік тому

    Hi, how to apply this idea to multiple sheets?
    Let say i have a 6 sheets as my data sheets
    Then one report sheet where it will copy data based on criteria..
    do i add all 6 sheets as my variable?

  • @imranisshack9828
    @imranisshack9828 7 років тому +3

    Can I download this workbook with the VBA Code?

  • @veaceslavstrungaru6293
    @veaceslavstrungaru6293 7 років тому +7

    Is it possible to skip selecting a single criteria, and just tell vba to look in a range and do the same?
    Like in instead of "athletename = reportsheet.Range("B2"). Value"
    having smth like "athletename = reportsheet.Range("B2:B50"). Value?
    providing that B2:B50 will have a list of all athletenames?
    P.S. new to vba

    • @russiannpcbot6408
      @russiannpcbot6408 8 місяців тому

      Way late to respond to help, but others might appreciate the answer. Cell B2 is a drop-down box where you choose the name of a single athlete. You can see it at 0:58. You can catch him using the drop-down towards the end of the video when talking about adding the second criteria.

  • @jaywebb6120
    @jaywebb6120 6 років тому

    Hi I have a workbook with 12 sheets each sheet is representative of a storefront. Each storefront contains data specific to that particular store front that is requested from a single vendor. Some of these items will be like items from storefront to store front and there will be a designated naming convention defined. Example "Pulled Pork" several storefronts will use Pulled Pork so what I want to do is parse each sheet and find all items that are like items and then add the requsitions on like orders and create a master order sheet as a separate sheet. All orders will be output on this sheet even if they are unique and contain item name ordered

  • @jenessajohnson3799
    @jenessajohnson3799 6 років тому +1

    Is there a way to search more than one words at the same time in the same column so the records for 2 persons?

  • @mohammedlabib4493
    @mohammedlabib4493 7 років тому +3

    how i can use a VBA to apply vlookup formula in sheet

  • @abdullahahmed9626
    @abdullahahmed9626 8 років тому +2

    This is awesome .. thanks for the video ! I just have one question.. how to make the search cell Insensitive ? means if you just type in some letter of a name or some number you get everything related to that when you click on the button!

  • @HybridFox_Ava
    @HybridFox_Ava 3 роки тому

    Hi , This is very good video. I just want to ask if how to extract the latest date or latest updated cells only with duplicate Athlete?

  • @lavdonbela2479
    @lavdonbela2479 Рік тому

    i dont want to clear searched data . What to change on the code ?

  • @magicassassin1947
    @magicassassin1947 3 роки тому

    Awesome code sirr😯😯😯😍😍

  • @mikelennon1078
    @mikelennon1078 4 роки тому

    Is it possible to get a copy of this file please?

  • @kS-vh1rq
    @kS-vh1rq 6 років тому +1

    Where can i download this vba code?

  • @josephrizal
    @josephrizal 6 років тому +1

    Can i download your VBA Code?

  • @andrewso9798
    @andrewso9798 8 років тому +1

    Hello, thanks for sharing this video. I am working on a project which involves using a user form. I want to paste the row data for the given text string that is selected on the listbox on the user form. How do I go about setting the range e.g. Teams = reportsheet.range("A2:A45").Value - Thank you.

  • @robertweingardner2621
    @robertweingardner2621 8 років тому +2

    Hello, Thank you for sharing your knowledge. I have learned a great deal watching both EAF #37 and #78. I have adapted your code to search for records as a cross reference sheet. I modeled my sheet to EAF #78. In my case, when an entry is entered and the data is searched, there is a good chance that what is being searched might not be found. Could you suggest what code I can add to allow for a 'record not found' scenario? Other than column references and sheet names, I used your code exactly.

    • @ExcelTricksforSports
      @ExcelTricksforSports  8 років тому

      There are some great search options with VBA - try looking on UA-cam for "Search Keyword using VBA" and you will find some good videos.

    • @rakeshkumarmadella7955
      @rakeshkumarmadella7955 7 років тому +1

      it's very confusing.. please don't look at it.. I didn't understood... he is not able to explain it properly

    • @LiteratureforAll
      @LiteratureforAll 7 років тому +1

      is it possible to copy required column only ,instead of "Range(Cells (i+1), Cells (i+12)).Copy" only column A, then Column D and Column F etc

  • @hottnott1
    @hottnott1 4 роки тому

    Please share workbook.

  • @MATTSCOTTR
    @MATTSCOTTR 4 роки тому

    Would the double criteria still work if you left 1 of them blank?

  • @chinyew7458
    @chinyew7458 8 років тому +1

    Hi. What if I want to combine part 1 and 2?
    If A and B Then
    Execute
    Elseif A or B Then
    Execute?

  • @Ingrams
    @Ingrams 6 років тому

    great code sir, iIve managed to make it run, thank you

  • @dannyfish2461
    @dannyfish2461 7 років тому

    thanks for the video I'm trying to write a vba basically the same as eaf 78 but some
    reason it error out if you could help I would appreciate it

  • @sanjitaghosh1114
    @sanjitaghosh1114 5 років тому

    Hi. I have tried this code. But it is showing error that object doesn't have this property.Please help.

  • @mikelennon1078
    @mikelennon1078 4 роки тому

    Does anyone knows how to create an Excel Macro that Searches Entire Workbook and Returns All Records which match a certain value from a drop down list? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employees ( many per organization) in another worksheet. How we can see the records of all employees in an organization by selecting an individual organization from a list or drop down box, etc. I would appreciate if your insights.

  • @visheshiitkgp
    @visheshiitkgp 6 років тому

    How can I do the same thing, if I want to conditionally copy from multiple excel and copy it into a separate master excel?

  • @itskeshav5001
    @itskeshav5001 3 роки тому

    Hello everyone, can anyone tell me how to connect this script to the button ? please help me.

  • @smileycat767
    @smileycat767 4 роки тому

    Why didn't you use a filter to extract your data of interest?

  • @sridharpamidimukkala
    @sridharpamidimukkala 3 роки тому

    Where to download the code

  • @9019828585
    @9019828585 5 років тому

    Hi , can you please share the file ?

  • @tannertucker22
    @tannertucker22 6 років тому

    This is excellent. Is there a way to paste results to the next blank worksheet. This way every search can be preserved? Thank you.

    • @JohnLythe
      @JohnLythe 6 років тому

      Yes you would just need to add a little bit of code that would first add a new/blank worksheet, name it and then use it as the paste destination. This video may give you some ideas ua-cam.com/video/N1d-EBULvtY/v-deo.html

  • @arvindkesharwani7328
    @arvindkesharwani7328 6 років тому

    Sir I have send a dummy file. In which I have assign a macro. But data not pasted in another sheet. Sir Please do help me for possible changes in the worksheet. If it is possible to changes them then modify the worksheet and email that worksheet

  • @Dopeboyz789
    @Dopeboyz789 6 років тому

    How do you match in any rows

  • @senseiladiessalon5952
    @senseiladiessalon5952 6 років тому

    how to make the search insensitive case ?

  • @antoniogomez7820
    @antoniogomez7820 7 років тому

    Hello ! How can I search multiple athletes at one time ?

  • @antarboua1988
    @antarboua1988 6 років тому

    Can i download this work book with the code!!!

  • @Kgosietsile83
    @Kgosietsile83 Рік тому

    Why is it that my code starts pasting data on row 2? Anybody? Kindly assist

  • @keithbroyles9899
    @keithbroyles9899 7 років тому

    Would this work on Google Sheets as well?

  • @gerardvaneggermond9967
    @gerardvaneggermond9967 5 років тому

    Hello sir, maybe you can help me, I have a worksheet with a range in which I want to place data, my range is from EA4: EE23, in column AE, I have the ID, EB, I have the names, the columns next to it I have 3 columns where I want to bring data. I have the following formula: = IF ERROR (Vlookup ($ EA4; INDIRECT ($ EB $ 2); 3; FALSE); "").
    In EB2 I have the range names. how do i solve this
    If you have another question, ask them.

  • @kirkd6285
    @kirkd6285 8 років тому +3

    your not selecting rows. you are selecting a single row using the range command. I was hoping to see the use of the Rows command.

  • @hollywoodmoviesinhindi215
    @hollywoodmoviesinhindi215 7 років тому

    how to copy values without formula

  • @gjsanu
    @gjsanu 3 роки тому

    This process would take a lot of time if database is huge.
    You should Rather try Array method. ✌️

  • @paulj625
    @paulj625 3 роки тому

    Very helpful!!

  • @rajeshkamat4682
    @rajeshkamat4682 5 років тому

    Can we set a creteria to auto select names in cell B2 in report sheet and save the work book with the filtered data?

  • @phucminh9481
    @phucminh9481 5 років тому

    cells(i,1)=< Application-defined or object error

  • @dtoscana69
    @dtoscana69 4 роки тому

    finalrow = Cells(Rows.Count, 1) .End(xlUp) .Row
    The line above returns an error. "expecting instruction end" translated from spanish

    • @JohnLythe
      @JohnLythe 4 роки тому

      Hi, check the syntax as the VBA font makes xlUp look like x1Up - lots of people encounter this error so there's a good chance this is what is also happening to you. Email me if you want the file (my address is in the video)

    • @dtoscana69
      @dtoscana69 4 роки тому

      @@JohnLythe Hi John, thanks for your reply, I tried that but it still returns the same error. I'll send you an email to get the file
      Thanks again!

  • @zeyahaque1021
    @zeyahaque1021 6 років тому

    Plz share this file

  • @hanselsantoso5238
    @hanselsantoso5238 7 років тому +2

    Why is it when i try to execute it, it says "Sub or Function not Defined"?

    • @hanselsantoso5238
      @hanselsantoso5238 7 років тому +2

      Well i did use different sets of datas but it should be fairly similar steps. Any inputs?

  • @MrHellboye
    @MrHellboye 7 років тому

    can you please show how to do exact same-thing you did in this video but data is on more than 1 sheet

  • @panddaisy
    @panddaisy 3 роки тому

    #78

  • @hotneil2076
    @hotneil2076 5 років тому

    Please email me the file, I have sent you the request email.