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.
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
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.
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
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
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?
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
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.
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
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!
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.
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.
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.
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
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
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.
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)
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.
Man! Thank you very much… you saved thousands of manhours from my daily workflow! Please keep it up!
you explained all the processes... very helpfull for a beginner in VBA. Thank you!
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
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.
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
Thank you
ExcelTricksforSports. its works
How can we make this code search multiple sheets.
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
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?
Can I download this workbook with the VBA Code?
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
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.
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
Is there a way to search more than one words at the same time in the same column so the records for 2 persons?
how i can use a VBA to apply vlookup formula in sheet
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!
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?
i dont want to clear searched data . What to change on the code ?
Awesome code sirr😯😯😯😍😍
Is it possible to get a copy of this file please?
Where can i download this vba code?
Can i download your VBA Code?
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.
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.
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.
it's very confusing.. please don't look at it.. I didn't understood... he is not able to explain it properly
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
Please share workbook.
Would the double criteria still work if you left 1 of them blank?
Hi. What if I want to combine part 1 and 2?
If A and B Then
Execute
Elseif A or B Then
Execute?
great code sir, iIve managed to make it run, thank you
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
Hi. I have tried this code. But it is showing error that object doesn't have this property.Please help.
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.
How can I do the same thing, if I want to conditionally copy from multiple excel and copy it into a separate master excel?
Hello everyone, can anyone tell me how to connect this script to the button ? please help me.
Why didn't you use a filter to extract your data of interest?
Where to download the code
Hi , can you please share the file ?
This is excellent. Is there a way to paste results to the next blank worksheet. This way every search can be preserved? Thank you.
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
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
How do you match in any rows
how to make the search insensitive case ?
Hello ! How can I search multiple athletes at one time ?
Can i download this work book with the code!!!
Why is it that my code starts pasting data on row 2? Anybody? Kindly assist
Would this work on Google Sheets as well?
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.
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.
how to copy values without formula
This process would take a lot of time if database is huge.
You should Rather try Array method. ✌️
Very helpful!!
Can we set a creteria to auto select names in cell B2 in report sheet and save the work book with the filtered data?
cells(i,1)=< Application-defined or object error
finalrow = Cells(Rows.Count, 1) .End(xlUp) .Row
The line above returns an error. "expecting instruction end" translated from spanish
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)
@@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!
Plz share this file
Why is it when i try to execute it, it says "Sub or Function not Defined"?
Well i did use different sets of datas but it should be fairly similar steps. Any inputs?
can you please show how to do exact same-thing you did in this video but data is on more than 1 sheet
#78
Please email me the file, I have sent you the request email.