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 236 comments
Rana - October 15, 2018

Dear Sir, Jon. Thank you for video2. This subject is very intresting. I went through with practice file. Some of code I manually typed as per your demontration and it works like a magic. I do not know whether I’ll go through like this in another file?. If I not then I will come back to re do with this video. I am worried but very much hope that your will not remove from that file. If you have to remove the this video, then your good instruction awaiting what shoul I do. Many thanks.

Reply
George - September 3, 2018

Pretty informative.

Reply
sarcs - August 3, 2018

Nice Video. However, it is good to keep integer instead of Long for the sheet count. It may be good idea to keep long for number of rows or finding the last rows.

Reply
Walter - July 22, 2018

Thanks for the useful information.

Reply
Karl - July 8, 2018

It worked the first time. Very pleased. However, I’m a little fuzzy on the (dot) Formula syntax. Specifically:

Range(“A3”).Offset(lsht, 1).Formula = “='” & Worksheets(lsht).Name & “‘!D10”

What does ‘!D10 refer to?

Reply
Kyle Jacobs - April 16, 2018

Awesome! The only issue I am having is that I am trying to even automate it even further.

I have added the below code above the code used in your example to create a Summary sheet if it doesnt exist:
“On Error Resume Next
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = “Summary”
Sheets(“Summary”).Select
Sheets(“Summary”).Move Before:=Sheets(1)
Range(“A:A”).ClearContents

When I run the code, it appears run the code above but it stops at your code. However, if I manually step through the code using F8 it will run it all creating the list.

Reply
Kyle - April 16, 2018

I have tried adding some code to Create a summary sheet if one is note already present. To just automate it one step further and avoid spelling issues spaces, etc…

This is code I have pasted above the code provided in the lesson.

[Code]
On Error Resume Next
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = “Summary”
Sheets(“Summary”).Select
Sheets(“Summary”).Move Before:=Sheets(1)
[/Code]

This issue I am having is if I run the code, it just run the code that I added not your code; however, if I step through it using F8 it will execute your code.

I dont know if on Error resume next is the best code to use; I am still a beginner.

Reply
    Jon Acampora - April 19, 2018

    Hi Kyle,
    If you are running that code on a different workbook from the workbook the code is in, then you will need to remove the ThisWorkbook.Activate line from my macro. That line is activating the workbook that the code is in, which could be different from the active workbook you want to run the code on.

    Here is a recent video on VBA Context that explains this concept in more detail.

    The On Error Resume Next will bypass any errors that are raised below that line. It’s typically only used when you know there could be an error raised, like if the Summary sheet already exists.

    You will want to add the following line below the line that renames the sheet.

    On Error Goto 0

    That resets the error handling so errors will be raised on lines below. Otherwise you just have blanket error handling that might cause additional errors.

    There are also other ways to check if a sheet exists, without having to first add a sheet. That way you don’t have an extra blank sheet if the Summary sheet already exists.

    The following is a function to check if the sheet exists. This is going to be a more advanced technique, but allows us to first check for the sheet before adding a new one.

    Function SheetExists(WorksheetName As String) As Boolean
    
        On Error Resume Next
        SheetExists = (Sheets(WorksheetName).Name <> "")
        On Error GoTo 0
    
    End Function

    I don’t have an article on that yet, but I explain more about functions and how to use them in The VBA Pro Course. I hope that helps. Thanks again!

    Reply
Tekena - April 14, 2018

Hello, thanks sharing all these useful information. I recently watched on of your videos on pivot tables and slicers. I’m trying to connect the slicers to all the pivot tables and chart in my workbook (using report connections), but not all the worksheets are showing up in the dialog. Only four out of my 5 pivot charts are showing up. Please how can i resolve this thank you.

Reply
Wouter - April 8, 2018

Hi Jon!

Enjoying the videos so far but I have a question. Is it possible to exclude the Summary worksheet from being counted and pasted, etc. The summary worksheet has a very different lay-out from the other worksheets and right now, I keep pasting over other data…
Thanks in advance!
-Wouter

Reply
    Jon Acampora - April 10, 2018

    Hi Wouter,
    Great question! Yes, you can add an If statement to exclude a specific sheet by it’s name. Here is some code.

    For lSht = 1 To lCount
      If Worksheets(lSht).Name <> "Summary" Then
        Range("A3").Offset(lSht, 0).Value = Worksheets(lSht).Name
        Range("A3").Offset(lSht, 1).Formula = "='" & Worksheets(lSht).Name & "'!D10"
        ActiveSheet.Hyperlinks.Add Anchor:=Range("A3").Offset(lSht, 0), _
                                    Address:="", _
                                    SubAddress:="'" & Worksheets(lSht).Name & "'!A3"
      End If
    Next lSht
    

    That If statement is saying if the sheet is the loop is NOT <> named Summary, then run the code below. It basically excludes the Summary sheet.

    If you are excluding multiple sheets, or the Summary sheet is not first in the workbook, then you will get blank rows in your list. To avoid this you can loop through the worksheets using the worksheet object, then use a counter variable inside the If statement to increment the rows only when the conditions are true.

    Here is some code.

    For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> "Summary" Then
        Range("A3").Offset(iRow, 0).Value = ws.Name
        
        'Counter variable to increment rows
        iRow = iRow + 1
      End If
      
    Next ws
    

    I hope that helps. 🙂

    Reply
Nang - March 30, 2018

I’m also excited like Andy to test this out when in the office on Monday.
Looking forward to the third video.

Reply
Rupert - January 29, 2018

This works well for me. However, what if my values are not always in the same cell on each tab? How do you code to find the last value in column D on each tab?

Reply
Diskman - January 21, 2018

Hi Jon, many thanks for your such kind of clear and specific tutorial on teaching VBA. Regarding one thing that confuses me a lot, how shall we recognize the sheet(worksheet) number in order? Every sheet just gets its own sheet number (Sheet 1, Sheet 2, ….) with a name as well, like Sheet1:=Summary, Sheet21:=Robert Zare, Sheet22:=Nancy Freehafer…. as shown on the VBA project window (Top-left side). However, when you consign a For-loop : [For lsht=1 To Worksheets.count… Next], which runs with following the sequence of those sheets being permuted on this workbook, like “Summary”, then “Andrew Cencini”, then “Anne Larsen”, then “Jan Kotas”…etc. Also when we assign a sheet with doing something, like naming somewhat sheet by “Worksheet(1).Name=”XXX”, Worksheets(2).Name=”YYY”, which also goes through the way as the latter one as described previously. So my question is:

What does the sheet number shown on the VBA Project Window work for? Or it works for nothing, shall we follow the actual sequence of how those sheets being lined-up on the workbook?

Reply
    Jon Acampora - January 22, 2018

    Hey Diskman,

    Great question! The VBA Project Window contains two names for each sheet.

    Codename(Sheet Name)

    The Codename can be used in the VBA to reference the worksheet as well. The advantage is that the user cannot change this name. So we don’t have to update the code if you or the user does change the sheet name.

    I will be sending an email with a link to page with some bonus videos on referencing common objects. One of the videos explains this in a little more detail.

    The For Next Loop will loop in the order of the worksheet tabs in the workbook, from beginning to end.

    I hope that helps. 🙂

    Reply
Mountaga - January 15, 2018

Hi Sir Jon,

This is just awesome. Very clear and very easy to follow.
Looking foward for the next video.

Thanks a lot !!!

Reply
Ken Farrell - January 1, 2018

Hi Jon, I trying to add the Hyperlinks and I’m getting and air message stating Run-time 424 object required. Can you please provided me with suggestions to fix this issue?

Thanks,

Ken Farrell

Reply
Nick - November 23, 2017

Hi Jon

Thanks for the course.

Unfortunately I’m getting;

Run-time error ‘438:

Object doesn’t support this property or method

When trying to reference the Total Sales figure on each sheet – falls over on the first loop.

This is my code;

Sub List_Sheets()
‘List all sheets in the workbook

Dim lCount As Long
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).Fromula = “='” & Worksheets(lSht).Name & “‘!D10”

Next lSht

End Sub

Any ideas?

Nick

Reply
    Greg - January 29, 2018

    i don’t know if you did an exact copy of your code to here, but I see the “Formula” is spelled wrong (“Fromula”). Could that be the issue?

    Reply
Jeff Pratt - November 14, 2017

Jon,
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?

Reply
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

Reply
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

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

Reply
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
      MARION - February 15, 2017

      Thank you too 🙂

      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: