Great detail and comments for a very useful tool. I love all the time taken to explain what’s happening. Am looking forward to video 3.

Fantastic , This is going to my life easier!!!!!

Dear all
I have one sub test, but it alert me that: end if without block if
Please help me correct this sub
many thanks

Sub test_delete_row()
Dim icountrow As Long
Dim irow As Long
icountrow = Row.Count
For irow = 1 To 10
If Range(“a5”).Offset(icountrow, 0).Value = “” Then Range(“a5”).Offset(icountrow, 0).EntireRow.Delete Shift:=xlUp
End If
Next irow
End Sub

Hi Jon,

I really would like to thank you for your videos, these are really very helpful. I have messed in a problem and would like to ask for your help. That is:

I have a long list of items’ name in column A having different colors background where items are repeating and number of those items in column B with default background. I have to calculated sum of those numbers for different item on the basis of background color of items’ name. Problem looks like as:

Column A Column B
Pen in Red color Background 400
Pencil in Green BG 500
Book in Ren BG 300
Eraser in Green BG 200

Here, I have to calculate the sum of column B on the basis of color of column A.

I hope to for VBA code from you in this regard.

really good lesson. while watching, I was hoping you would also address the movement of value to bring back – eg in the first quarter the summary number is located in a different cell than 2qtr and each qtr it moves to the right. using a variable vice D10 would allow it to work for however many columns there are

Thanks for your video Jon! Is this macro something that could be recorded in a personal macro workbook or would that be unwise? I assume I’d have to change the “activate” code in some manner. I work in an area where I build new models from scratch and need to do a table of contents like this approximately monthly – but always in a totally new workbook with tabs that vary each time. It’d be helpful to have this in my personal macros and not have to go back and find my previous model to recopy the code.

HI Jon,

I regularly watch your tutorials and they are fun .
Please tell me where I can found The VBA pro tutorials.Can you please share me the link to those videos to my email id “[email protected]”

I am waiting for that.

Jon,

I downloaded the workbook and was able to see the Modules folder. I cannot, however, see the Module folder in any of my my other
macro-enabled workbooks. Is there a setting which needs to be changed?

Thanks, Joan

Great video, learn a lot more can’t wait till the next video

Hi Jon,
Thank you for your videos. I know that I should be able to do amazing things with Ecxel if I knew how to write code. Your videos are a very good introduction.

I gave myself the task to automate the hyperlink from each sheet back to the Summary sheet.

This is what I came up with. It seem to work too 🙂

The last part i just to tidy up the Summary page which gets a couple of extra hyperlinks otherwise.

Sub List_Sheets()
‘List all sheets in the workbook

Dim lCount As Long ‘Long is short for Long interger
Dim lSht As Long ‘Long holds a number

lCount = Worksheets.Count

For lSht = 1 To lCount
Worksheets(1).Range(“A3”).Offset(lSht).Value = Worksheets(lSht).Name
Worksheets(1).Range(“A3”).Offset(lSht, 1).Formula = “='” & Worksheets(lSht).Name & “‘!D10”
‘Underscore character allows us to continue code on a new line
Worksheets(1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Range(“A3″).Offset(lSht, 0), _
Address:=””, _
SubAddress:=”‘” & Worksheets(lSht).Name & “‘!A3”

‘Add Summary hyperlink to every employee sheet
Worksheets(lSht).Select
Range(“A3″).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:= _
“‘” & Worksheets(1).Name & “‘!A3″, TextToDisplay:=”Summary”

Next lSht

Worksheets(1).Select
Range(“A3:B4”).Select
Selection.ClearContents
Selection.ClearFormats
Range(“A4”).Value = Worksheets(1).Name

Hi Jon,

It was a good presentation, interesting. I hadn’t used “hyperlinks” at all in my workbooks so that was a good feature for me. It also emphasises the important role that the Macro Recorder can play.
Thanks
Dave