Macros & VBA Training Series Part 2:
Automate Excel: How to Use Loops to Create a Summary Report
Double-click video to view in Full Screen HD.
Download the file used in this video:
Macros & VBA Training Series
Intro to VBA:
Writing Your First Macro
Video 2
Automating Tasks:
The Summary Report
How can we automate this process further?
Please leave a comment below with your answer, and any questions. Thanks!
I’m loving these videos. The way you explain macros is so helpful. I’ve taken many classes but they usually go to fast or assume you understand what a loop, a string, workbook, worksheet, range, etc already is. Thanks for explaining what each is and how they work.
I use excel in all my jobs and the various places I volunteer. Where I volunteer I have been implementing Macros so that the other volunteers can easily run reports without having to know or understand excel. (A long slow process for a beginner, but a great learning experience)
I work in a workbook that generates and names a new sheet each week. All the information is entered on this sheet. To make it easy for all volunteers we have the reports at the bottom of the spreadsheet, so they don’t have to navigate back and forth between numerous sheets. The reports are run by macros. Each part contains a loop. One that takes info from last 4 sheets and calculates the info. One that takes the info from the last 52 sheets and calculates the info.
They have a couple bugs i am still trying to work out but I’m getting there.
However, our supervisor wants one more piece of info. He wants the info from the first sheet of the current year (sheets are named with dates each week = m/d/yy) to the current sheet. So one week it would be 5 sheets the next week 6 sheets up to 52 sheets, when it would start over at 1. I can not figure out how to word the loop so it begins on sheet the first sheet of January of the current year through to the current sheet. How would I do this? It seems like there should be a way to do just about everything in excel.
UserForm to Select Sheets, nice because it can float to 2nd or 3rd screen while you view sheet on screen 1.
a very instructive and helpful video, made of professionals. Thank you a lot, excel campus is reinforcing my will to learn more and more about excel. I believe through systematic training that can improve tremendously excel knowledge and handling data and financial worksheets skills
Thank you for such great tutorials. Q: Is there a code you can write to exclude certain worksheets in a workbook when creating a Summary worksheet?
Great tutorial!
Thank you!
can you guide me regarding how to code for ‘goto and if and how to give subtotals for the selected columns in a sheet
Dear Sir,
I came to know you already completed the very next step, create individual files for individual customer ledger.
We can extend it by SOS statement. What is the balance we have to receive from our customer ( Sales Amount – Receipt (cash /Cheque/Transfer) =Balance to receive.
We can extend to Age wise report ( balance pending for the period (0-30,30-60,60-90) and above.
I create such files by recording macro. It not seems look nice.
I will send those files to you.
I came to U. A. E With visit visa. Here I get a job to simplify Excel work. Even though I am accountant and don’t know Vba macro
I get a job in U. A. E to simply Excel work. Now I create Day Book Form from which individual ledgers and sales report with graphs. Individual ledger and Age wise analysis based on the period we have to receive the pending amount.
Your course (1 to 3) is very much useful. Thank you. Want to learn more and get mastery. Please convey the steps.
Very useful tutorial. But I could not follow wherefrom you inserted (Paste) the hyperlink in your code.
Hello Jon,
Thanks for this very educational video.
One thing I noticed is that the generated summary lists include hidden work sheets. The links are still correct, but because the worksheets are hidden, you can’t jump to the hidden work sheets.
I’m trying to add an option to implement or skip them by using the visible properties xlSheetHidden Or xlSheetVeryHidden to filter it out.
Because my offset row counter changes then as well, it’s a little challenging, but I’ll figure it out.
Looking forward to Video 3.
Thank you very much for the excellent tutorials. I am in a better position to excute my work more professionally. Can you please allow access to the 3rd video presentation.
Hi, I’m trying to learn VB and Macros. I have a large data that i need to download daily and then put a summary of it. It changes daily so I need a Macro or Code to read the data and make it look like a nice report. Please help…
Thank you Jon, you have very methodical and simplistic approach, makes learning that much easier.
this is definitely helping me understand about micros and I’m very excited to learn. thank you for the time you took to make these videos
I would love to see how to do the when the total is not in the same cell on each sheet… Might just be use a vlookup with the same formula entering strategy?
Absolutely incredible! Very clearly explained. Thank you. I learned loads!
How is anyone supposed to open the workbook when it looks like this:
Download the file used in this video: [wpfilebase tag=file id=119 tpl=’simple_excel_file’ /][wpfilebase tag=file id=67 tpl=’simple_excel_file’ /]
Can’t even do the video without that
Very nice teaching technique that i have seen, thanks Jon your videos as very helpful for my daily work & it saves my work time.
In video 2 at about 9:35 you enter a reference to Andrew’s worksheet but do not explain the syntax of the reference.
In a previous comment I mentioned a disparity in the list of worksheet names and what was generated. Oooops, my error; I should have looked below.