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
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!
Hi Jon! Thank you so much for your awesome help… may God reward you so much!
Thank you very much, helpful site.
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
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! 🙂
Thank you too 🙂
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.
Is there a file that contains the worksheets for video #2?
thank you very much for your excellent tutorial
Love the training, can’t wait for the next video.
Thank you Jon,This is really a magic and awesome that every Excel Enthusiast must learn.
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,
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.
Great teaching. Thx.
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 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.
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 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 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 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.
Thanks Very much Jon. This is GREAT.
Question. What is I need to get different information from multiple workbooks? Is this possible using VBA?
this is invaluable tutorial, recommend it to everyone terrified of programming
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?
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!
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
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
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?
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?
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 Sir Jon,
This is just awesome. Very clear and very easy to follow.
Looking foward for the next video.
Thanks a lot !!!
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. 🙂
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 Rupert,
Great question! Here is a video series on how to find the last used cell on the sheet with VBA.
You can set the address of the last used cell to a variable (sLast). Then use the variable in the code for the formula.
“='” & Worksheets(lSht).Name & “‘!” & sLast
I hope that helps get you started.
I’m also excited like Andy to test this out when in the office on Monday.
Looking forward to the third video.
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 lShtThat 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 wsI hope that helps. 🙂
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.
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.
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 FunctionI 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!
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.
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?
Thanks for the useful information.
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.
Pretty informative.
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.
very good exercise
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!
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.
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?
May i know the VBA Macro Code to copy Summary hyperlink to all other worksheets?
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
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.
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.
Awesome video Jon.its very clear and Cristal example.
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
Hi I would like write a macro for getting LinkedIn profile to excel.
Can u pls help me
Hi Jon,
If all the sheets does not fill with the different number of rows. How can we add the formula in Vba
Exmaple Instead of D10 other worksheets has D15 something like that how to write a code for that.
Best Regards
Vinod