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:

The Summary Report Macro - Practice.xlsm (114.8 KB)

The Summary Report Macro - Solution.xlsm (128.5 KB)

Macros & VBA Training Series

Video 1

Intro to VBA:
Writing Your First Macro

Video 2

Automating Tasks:
The Summary Report

Video 3

Userforms:
The Exports Sheets App
Coming Soon!

How can we automate this process further?

Please leave a comment below with your answer, and any questions.  Thanks!​

Click Here to Leave a Comment Below 212 comments
Oluwashina Fajewe - September 13, 2017

Thanks Jon!
I some monthly workbooks and I comparison between the month i.e July this year with July last year and June this year. How can I use VBA to achieve this task without necessarily opening the previous month and year workbook to copy and paste the figures into the current workbook?

Reply
Tina - September 13, 2017

this is invaluable tutorial, recommend it to everyone terrified of programming

Reply
Jahnigh - August 1, 2017

Thanks Very much Jon. This is GREAT.

Question. What is I need to get different information from multiple workbooks? Is this possible using VBA?

Reply
Chris Matthews - June 28, 2017

Hi John
Excellent video. It would be nice to add the summary sheet all formatted from just the basic sales files along with being able to to total the sales for each rep instead of having a locked cell reference. That way we would not need to adjust if there are more or less sale for a certain rep.

Reply
Spencer - June 13, 2017

Hi Sir,

I really like so much in the way you teach the macro above. But can you show us if we do have a few workbook how we can flexible compile all the data as one workbook although the workbook are rename differently.

Reply
Daniel - June 3, 2017

Hi Jon,

Thanks for the excellent video tutorials!

My question is; if we have a data set how can we copy the value in the first cell of a column then paste three copies of that value in a new row, and then copy the second cells value and paste three copies in the new column, then continue on a loop until all values have been copied and and pasted three times?

for example, the column on the left would be original data and column on the right would be the new data;

1 1
2 1
1
2
2
2

Thanks,
Daniel

Reply
Le - May 23, 2017

Hi Jon,

It is a excellent Video! I would like to learn a list of excel shortcut, which I use the keyboard to get to excel worksheet so I can work quicker. Hope you can help. Many thanks

Kinds regard,

Le

Reply
Mariska Vermeer - April 30, 2017

Hi Jon, Thanks a lot for sharing this with us!
Got tabhound that does all this work with a click of a button 🙂
But…I do have a question.
I like the totals behind the names and I would like to do the same. However, my totals are not always in the same cells. Is there a way perhaps that can solve this?

Thanks again Jon, looking forward to see more videos

Mariska

Reply
    Drei - May 23, 2017

    Hello Mariska!

    Vlookup function can be useful for this instead of the formula used by Jon. Searching through columns with a criteria like “Total YTD sales”. Only that, your Total amounts must be in the same column in all of the sheets. So far that’s what I can think of.

    I hope that helps. 🙂

    Reply
    Jon Acampora - May 25, 2017

    Hi Mariska,
    Great question! Yes, this is definitely possible. I have a video series on how to find the last used cell on a sheet. The Range.Find method that is presented in those videos can be used to find any cell on the sheet. It is similar to using the Find window, Ctrl+F, in Excel.

    Reply
Scott H. - April 13, 2017

Great teaching. Thx.

Reply
aditya suman - March 30, 2017

hi jon,
the summary sheet macro video is very helpful but i want to know that in all sheets except summary the total sales was fixed in d10 cell.what if different sheets have different cells associated with total sales.

and kindly share some video regarding VBA macros which are helpful for the job.

Reply
Alan - March 21, 2017

That video was incredibly useful.
However, I am stuck on something. Using VBA how would you copy two cells next to each other and paste them into the first empty row of a different worksheet?
The macro needs to loop through a number of rows and copy/paste multiple adjacent cells worth of data.
This is my current code
Sheets(“Potential Proposals”).Cells(i, “A”).Copy Destination:=Sheets(“Pending Proposals Log”).Range(“B” & Rows.Count)

I can’t figure out how to expand it to copy the cells in both columns “A” and “B” while using the “i” variable.
what am i doing wrong?

thank you

Reply
T P Redkar - March 15, 2017

Thank you Jon,This is really a magic and awesome that every Excel Enthusiast must learn.

Reply
Catherine - March 13, 2017

Love the training, can’t wait for the next video.

Reply
khiari - March 2, 2017

thank you very much for your excellent tutorial

Reply
Paul - February 20, 2017

Is there a file that contains the worksheets for video #2?

Reply
Dave G - February 7, 2017

Hi Jon
Great training video #2, love it. How about the other way round, if we started with the summary; list of names and amounts, automate creating sheets in their names and enter the amount in a specific cell on their sheet?

Reply
    Jon Acampora - February 15, 2017

    Thank you Dave! Yes, that can definitely be done with VBA. We would loop through the list, use the Add method for Worksheets to add the sheets, rename the sheets, then copy the values to the new sheet.

    Reply
MARION - February 7, 2017

Hi Jon,

Awesome video! Learning alot but could you kindly provide the work book with the raw data ( not the already compiled finished version). I think one learns better by practicing as they watch. Thank you

Reply
    Jon Acampora - February 15, 2017

    Great suggestion Marion! I just added the Practice file to the page. You are absolutely correct that we learn faster by practicing writing VBA code. Thanks again! 🙂

    Reply
Imran - February 4, 2017

Thank you very much, helpful site.

Reply
Abel - January 31, 2017

Hi Jon! Thank you so much for your awesome help… may God reward you so much!

Reply

Leave a Reply: