Create reports from raw data automatically with loops, auto-filter and VBA

In this training video we learn how to create reports from raw data automatically using loops, auto-filter and VBA in Excel. The method to create reports using auto-filter and VBA is powerful and the report is generated in seconds.
Complete details available at this link:
www.exceltraini...

[@ashrafulalam4884]

It’s really very useful method! Appreciated

[@Rajan-kk8nl]

Your all tutorial video is very very nice sir. Thank you very much for published videos.

[@Exceltrainingvideos]

So nice of you. Please share with your friends too.

[@nadermounir8228]

Very nice tutorial 👌 Thank you for your hard work

[@Exceltrainingvideos]

So nice of you

[@Gestapoz1988]

I have a question I have a main table that contains all the data. For example, four cities and every city has a range of cars. And 4 secondary tables on every page there is only one table that has an existing cars in each city information. the question is: In the main table When I change a car "A" from the city "1" to the city "2", is removed from the table of city"1", and add them to the city Table "2" automatically. the changing is automatically. Thank you .

[@Aku1985Khera]

Got you point, thanks Sir. Is there a way (GUI) - that I wish to add 4 checkboxes and user has to select which criteria he wants to filter and according to that the macro filter the data ??

[@jmarrero1108]

Excellent video like always. Got a question. Would it not be better to create a pivot table or power query instead?

[@Exceltrainingvideos]

Why not if you know how to do that well!

[@vinodganger9181]

Dinesh Kumar Takyar sir I tried same as your written can you give me link to same thankful in advance

[@laurenmk5096]

Thanks thats helpful but wouldn’t it be faster if we just sorted category by bakery item and then copied the data to bakeryitem sheet? Is there a scenario were it would be more efficient to write a for loop code?

[@Exceltrainingvideos]

This could be the solution you are looking for: kzbin.info/www/bejne/jnSYg5R7mLKKmNk

[@edtardaguila3599]

hi sir what about if my criteria is from dynamic value from a combobox or textbox? and auto filter the extracted data to a new workbook ? thanks in advance

[@safiyyahaziz5477]

Very useful video. However, is there a way I can add on data from the last line without deleting the previous data. I have to update the data every month but I would like the previous months data to stay the same and only update with current data. Not sure if this is possible.

[@Exceltrainingvideos]

Yes, you can

[@dewansadiakarim2209]

which excel version you are using? in my excel sheet i do not have any tool named developer

[@Exceltrainingvideos]

This link will help to display the developer tab: www.exceltrainingvideos.com/how-to-display-developer-tab-in-microsoft-excel-2010/

[@tannertucker22]

This is excellent. Thank you. Very often I have to select more thank one category like bakery and dairy and meat. How would I modify code please. Thank you very much.

[@Exceltrainingvideos]

You can use an 'IF' with 'AND'.

[@tannertucker22]

@@Exceltrainingvideos Thank you. I was wondering if there was a way around IF and AND - no problem. Thank you for all of your videos!

[@vijayabhaskarraochava5211]

Dinesh Sir, Namaskaram please make a video regarding “convert data from a .doc table to xls table (the.doc table containing text)

[@Exceltrainingvideos]

kzbin.info/www/bejne/b4KtiaCpmN-Uhpo

[@naveenchawla8534]

Hello Sir thankyou For Uploading Video Videos are Very helpful. Sir I Want to make macro which browse two different workbooks (as file choose by using fileDailog Function) and compare those two files if any difference in any specific columns Just changes their color.

[@krn14242]

Thanks Dinesh. Very useful indeed.

[@kasim1060]

I stuck at filter level. My Header rows starts from A6, I have done all required changes but not debug at filter level. Any suggestion to solve problem?

[@Exceltrainingvideos]

Can you share your code?

[@numanshahid4667]

Thank it's really helpful but I want to creat this automatically transfer to there relevent sheets instead of selecting everytime as u select 1st bakery item then fish, I want as i write in day book whatever I enter its automatically transfer to there relevent sheets or ledgers. Kindly guid me or refer video.

[@Exceltrainingvideos]

www.exceltrai ningvideos.com/transfer-data-from-user-form-to-multiple-worksheets-in-excel-workbook-using-vba/ Or searh www.exceltrainingvideos.com

[@satyakurumanna991]

Thank you sir. It was very useful for me.

[@Couragetogrowth]

Can we put a short cut key to open the search box?

[@rajeshchandnaithani5434]

yes

[@vinodganger9181]

I tried but I get problem in plement can you give me Linkin to this subject

[@nandafusion]

Sir I am only getting the last row in the new sheet.I have used Command Button. Can you please explain me.

[@Exceltrainingvideos]

Check your code.

[@MohdArif-Observer]

Good morning, I want to know that how we bypass the searched title while coping data to same titled

[@Exceltrainingvideos]

I didn't understand your question with respect to this training tutorial.

[@MohdArif-Observer]

While using offset we transfer the entire data of row with criteria. I don't want the criteria to be transferred to specific file as criteria itself is a file

[@RakibHasan-ct8ck]

If I want to find which month I sell how many products then how to put monthly and yearly report???

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/using-match-function-for-data-analysis-in-excel/

[@vijayabhaskarraochava5211]

Finest Sir Namaskaram please make a video regarding “convert data from a .doc table to xls table (the.doc table containing text)

[@Exceltrainingvideos]

This training video will help: kzbin.info/www/bejne/b4KtiaCpmN-Uhpo

[@vmjamshad1]

Hi Sir, This video is really helpful! I was just thinking if i can export the report without filtering the items. If i want to export all the items in one click to their individual sheets. Thanks again for these wonderful videos.

[@Exceltrainingvideos]

Search www.exceltrainingvideos.com

[@vmjamshad1]

Thanks Sir

[@SebAnt]

www.rondebruin.nl/win/s3/win006.htm create new sheet for all unique values

[@alexandergonzalojimenezcas3984]

yo quiero trabajar todo desde la celda A8 pero no me responde tengo problemas en la parte Sheets("REGISTRO1").Range(Cells(8, 1), Cells(lastRow, lastColumn)).AutoFilter Field:=20, Criteria1:=myvalue Sheets("REGISTRO1").Range(Cells(9, 1), Cells(lastRow, lastColumn)).Copy desde esa parte hacia abajo como seria ?

[@Exceltrainingvideos]

este hipervínculo ayudará: www.exceltrainingvideos.com/create-reports-from-raw-data-with-loops-auto-filter-vba/

[@alexandergonzalojimenezcas3984]

@@Exceltrainingvideos would you may being a little more especific where I do the change exactly

[@alexandergonzalojimenezcas3984]

its working now but there is a litle bit of problem , its repeating into the right. could you help me please

[@anand6802]

So before this code do i need to create all sheets or it will be create automatically?

[@Exceltrainingvideos]

Search www.exceltrainingvideos.com

[@Excel-power-users]

Hello Dinesh, I tried to find the video for my current request. However I did't find it hence posting this request here. Requirement is : In Cell A1 the value is 10 so when I type any number say 25 in A1 it should give me the result as 35(old value+new value).

[@Exceltrainingvideos]

1. Define 2 variables as long 2. Enter value in cell A1 3. Assign this value to variable, let's say, x 4. Using an inputbox enter a new value and assign this value to the second variable, let's say, y 5. Next assign the value of the variable y to the cell A1 6. Now write the code: Range("A1")=x+y 7. You'll get the sum of the values in cell A1 I've done exactly what you described. Now write the complete code. I'm not writing the complete code because I don't want to make you lazy!

[@pamidighantamchenchubalaji9302]

Dear sir, From xls file i want to generate outstanding report for few items receipt all receipt data will be in single file Total amount of item - total receipts for a period and outstanding amount like this for around 10 items can you give me the code for the same please

[@Exceltrainingvideos]

Refer to this playlist: kzbin.info/www/bejne/aIWylYtnjq19h7M

[@gg2056]

Very helpful my friend!

[@peatize]

Hi Sir, Firstly thank you for these amazing videos that you post. I have a question if you could help in solving it. I am currently looking at ways where i can automate meeting(visits/calls) details with just a click. For example, I wanted to pick the keyword "apple" from the column general comments where it says " Rita had a call with Tom in regards to buying apple from a store nearby"... but when there is a long sentence how do we use VBA to find the word apple and move the entire row to apple sheet? Similarly there are comments entered differently but we have certain keywords to look for like "apple, mango, orange or grapes" and these keywords are used everytime in the meeting and each keyword has its own sheets too. Can you help in how to tell excel to pick sentences that has word "apple, mango, orange or grapes" in it and move the entire row to their respective sheets. Perhaps a video like this one would help many of us.. Thank you!

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/

[@heetsolvex2237]

Sir, Ihave tried with this my own daily register. But it comes with error 9( subscription out of range). Please help !!

[@Exceltrainingvideos]

Check your worksheet names or number of worksheets!

[@ahmadmacarandas6722]

hi sir, please advice regarding this, if we export all the items in one click without filtering them. same request with jamshad mohd. Hi Sir, This video is really helpful! I was just thinking if i can export the report without filtering the items. If i want to export all the items in one click to their individual sheets. Thanks again for these wonderful videos.

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/how-to-create-report-from-excel-data-sheet-with-vba/ Or do a search at www.exceltrainingvideos.com

[@TheAnthroheart]

Hi Dinesh Kumar Takyar, Thank you so much for your video and code. I want to acheive exactly what you have done. However went I create a sample workbook with the same named sheets and paste the two VBA modules, I get syntax errors. I have sample data in the "Day book purchases" sheet. Is there a support forum I can upload my workbook to and work through my errors? Thank you again sir.

[@Exceltrainingvideos]

This is not strange. I've mentioned this behavior many times.

[@TheAnthroheart]

I'm very sorry Dinesh Kumar Takyar, I didn't know that. I'm just starting to go through your wonderful tutorials and this is the first time I've struck this problem. It had me quite perplexed. I am very grateful for your tutorials. They're the best I've found.

[@SebAnt]

See www.rondebruin.nl/win/s3/win006.htm create new sheet for all unique values

[@wasifangel]

What if you want to skip one column in between?

[@Exceltrainingvideos]

Don't select that column!

[@nareshdurgapal1512]

Dear dinesh sir It is possible in this sheet if i want filter data by category in one sheet Like For filter Category : Soda soft Drinks Date item 03-Oct Coca cola sprite 400 ml 03-Oct coca cola coke 600 ml qty rate total 75.00 6.00 450.00 24.00 31.83 763.92 And below this next category for same sheet. Category : cooked food Date item qty rate total 03-Oct Arikadukka 75 6 450

[@Exceltrainingvideos]

These links will help: www.exceltrainingvideos.com/auto-filter-microsoft-excel/ www.exceltrainingvideos.com/advanced-filter-in-microsoft-excel/ You may search for VBA solutions at: www.exceltrainingvideos.com

[@remylebeau3710]

Good videos, helpful.

[@hemanthrajesi]

it's amazing sir, Sheets("day book"). Range(cell(1,1),cells(lastrow, last column)). Auto filter filed:=3, criteria1:=myvalue In this line runn time error 1004 Application defined or object defined error This msg will come Now what can I do sir Please give suggestion

[@Exceltrainingvideos]

Check your vba code. This link will help: www.exceltrainingvideos.com/create-reports-from-raw-data-with-loops-auto-filter-vba/

[@naveednak5113]

Hi Sir How are you? sir mai chahta ho ke sheet 1 mai ek listbox create karo oos listbox mai jitne bhe sheets he oos listbox mai show ho phir mai listbox mai se sheet2 ya sheet 3 karo select karo or oos selected sheet 2 mai se mujhe 2 page print karne he tou ap ese he ek tutorial banae mehrbani sir

[@Exceltrainingvideos]

These links will help: www.exceltrainingvideos.com/navigating-worksheets-using-combobox-activex-control/ www.exceltrainingvideos.com/display-all-worksheets-of-workbook-in-listbox-and-print-specific-worksheet/ Or search www.exceltrainingvideos.com or my KZbin channel goo.gl/5Jx1NP

[@ANDROSTENEDIOL]

Doesn't work in Excel 2003. Thank you for video but please warn people at the start of the video that script doesn't work in particular Excel versions. I have just wasted 2 hours trying to get this working.

[@Exceltrainingvideos]

I haven't found a reason why this should not work in Excel 2003.

[@ANDROSTENEDIOL]

@@Exceltrainingvideos Compile error: lastRow = Sheets(“Day book purchase”).Range(“A” & Rows.Count).End(xlUp).Row If Sheets(“Day book purchase”).Cells(i, 3) = “Bakery item” Then Sheets(“Day book purchase”).Range(Cells(i, 1), Cells(i, 6)).Select Sheets(“day book purchase”).Select Above lines are all red. Code is exactly to a dot same as yours and it doesn't work. Thank you for your work anyway.

[@krishnarajumst6878]

sir this is very excellent i want this excelfile i am a accountant

[@Exceltrainingvideos]

www.exceltrainingvideos.com/create-reports-from-raw-data-with-loops-auto-filter-vba/

[@KNPatel-iq2jv]

सर , मेरे पास एक सीट पर अलग और सेम पार्टियो के नाम है,उसके सामने दूसरी कॉलम cr ओर तीसरी dr है कोई पार्टी लेने और देने आती है तो इस ही सीट पर ऐट्री कर लेता हूं लेकिन मुजे कोई एक पार्टी का टोटल cr ओर dr निकलने केलिये बार बार फिल्टर करके निकलना पड़ता है तो - दूसरी शीट पर पहेली सीट वाली सभी अलग और सेम पार्टियो ऑटोमैटिक पार्टी वाइज आ जाये ऐसा करना है plz help me

[@Exceltrainingvideos]

Run a looping process like 'for.... next'

[@faizrehman9353]

sample file please

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/create-reports-from-raw-data-with-loops-auto-filter-vba/

[@goyaltraders3265]

Sir aap ka phone number kya hai

[@nageshkhurana3848]

Getting Run Time Error 9 subscripts out of range in find what commands lastRow = Sheets("PO Creation").Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=x1Part, _ LookIn:=x1Formulas, _ SearchOrder:=x1ByRows, _ SearchDirection:=x1Previous, _ MatchCase:=False).Row

[@Exceltrainingvideos]

Check your code and worksheet name carefully.

[@nageshkhurana3848]

Dinesh Kumar Takyar sir I have sent an email , request you to help me out . Thanks in advance