VBA Training Series – The Summary Report Macro (Part 2 of 3)

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

246 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,
    When run the below code
    Range(“b19”).offset(lsht,2). formula = “=’ ” & worksheets (lsht). name & ” ‘ !D57″

    It was asking me a” open a file”.

    Getting wrong value.

    Please help it out
    Thanks

  • Hello Jon,
    How do I select the year wise data using macros. it’s very easy to using pivot table but how do filter out using macro’s.
    How do this automation for this same.

  • Mr.Jon, your videos are very nicely done. As far as being clear and easy to follow, they are probably the best I’ve seen. How can I transform a database data or excel file data to a complete report with using macro + VBA?

    Thanks for your help.

  • Hi John

    I am learning so much faster watching your videos than I ever did with other VBA trainers/tutors, so a big thanks for that.

    Is there a way to do this “Summary Report” without producing the “0” in cell B4?

    Kind regards

  • Thanks for the video. I have a question. Why did you put formulas (=’Andrew Cencini’!D10) in the summary column (B3) rather than the values ($3,786.50) themselves?

  • Jon, your videos are very nicely done. As far as being clear and easy to follow, they are probably the best I’ve seen.

    Thank you. I look forward to more of them.

  • Awesome material! I’m trying to automate a ‘Master Project Tracker’. Essentially we have all our clients listed and we have a tab that categorizes the deliverables into 6 categories. Not every client will have the same deliverables, but I don’t want to have to copy and paste each one every time we have a new client. Is there a Macros I can write that will add the 10 ish lines I want from that specific tab? Also I’m trying to have the very first tab show what deliverable is coming due next and it tell me the client name, deliverable and the due date; could this be done? Love your work and look forward to hearing back!

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

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

  • 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?

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

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

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

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

  • 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

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

  • 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?

  • 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?

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

  • 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

  • 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

    • 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?

  • 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?

  • 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

  • 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

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

  • 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?

  • Thanks Very much Jon. This is GREAT.

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

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

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

  • 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

  • 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

  • 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

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

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

  • 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

  • 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?

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

  • 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