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

If you would like to support me in making more videos then you can buy me a coffee

Two VBA procedures are shown as a follow up from video #37
1. Loop through 500 rows, extract records using 1 criteria and paste them to another sheet
2. Loop through 500 rows, extract records using 2 criteria and paste them to another sheet

Check out some of my other work on Vimeo

[russiannpcbot]

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.

[gillesdhoker]

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

[familiapipajose]

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

[sajalmajhi]

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

[jaywebb]

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

[armotxa]

Thank you
ExcelTricksforSports. its works

[tronway]

How can we make this code search multiple sheets.

[leosanders]

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.

[petertaylor]

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

[gjsanu]

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

[mohammedlabib]

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

[veaceslavstrungaru]

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

[kirkd]

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.

[Ingrams]

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

[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

[mikelennon]

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.

[Kgosietsile]

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

[phucminh]

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

[imranisshack]

Can I download this workbook with the VBA Code?

[uppxmzp]

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?