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

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 216 comments
Jeff Pratt - November 14, 2017

Great video. I must have something incorrect. In A4 I get Summary, then Andrew Cencini. At first, I was getting the Cover tab, so I deleted it and saved the file again.

Here is my code and it matches yours, but it runs as if it is opening the file vs. just reading the next tab i.e. Andrew Cencini:

Sub List_Sheets()
‘lists all sheets in the workbook

Dim lCount As Long ‘holds a number
Dim lSht As Long ‘holds a number

lCount = Worksheets.Count

For lSht = 1 To lCount
Range(“A3”).Offset(lSht).Value = Worksheets(lSht).Name
Range(“A3”).Offset(lSht, 1).Formula = “='” & Worksheets(lSht).Name & ” ‘!D10″

Next lSht

End Sub

Any thoughts?

Jaap - October 17, 2017

I’m sorry solved it myself already, because I had been adding a column etc I first had hyperlinks in column C and A aswell, the formatting was still ther although I had deleted the data and the macro had put in the new data. Sorry for the inconvinience

Jaap - October 17, 2017

Thank you Jon for your very usefull video’s and teaching. I followed along and added a sheetnumber in row A. But I get a slightly differented result, al my columns are formatted as hyperlink but only column B works as a link. What went wrong?

ActiveSheet.Hyperlinks.Add Anchor:=Range(“B3″).Offset(lSheet, 0), Address:=””, SubAddress:= _
“='” & Worksheets(lSheet).Name & “‘!A3”

Best regards

Andrea - October 2, 2017

Hi Jon,

This is my first exposure to VBA, although I have been planning to learn for a while now. Can VBA format the sheet as well? Thanks!

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?

Tina - September 13, 2017

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

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?

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.

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.

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


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,


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


    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. 🙂

    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.

Scott H. - April 13, 2017

Great teaching. Thx.

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.

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

T P Redkar - March 15, 2017

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

Catherine - March 13, 2017

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

khiari - March 2, 2017

thank you very much for your excellent tutorial

Paul - February 20, 2017

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

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?

    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.

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

    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! 🙂

Imran - February 4, 2017

Thank you very much, helpful site.

Abel - January 31, 2017

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


Leave a Reply: