Highline Excel 2016 Class 26: Macro Recorder Basics & Copying VBA Code From Internet

In this video use Macro Recorder to record six Macros:
1. (00:12) Basics for Recorded Macros (VBA)
2. (05:55) Absolute Reference Macro: Format report that always has the same number of columns and rows. This Macro is stored in Personal Workbook.
3. (18:32) Absolute & Relative Reference Macro: Format report that that has an unknown number of rows
4. (26:38) Relative Reference Macro to convert vertical records into a proper data set with horizontal records
5. (31:09) Search for VBA Code online: Function to spell out numbers as words
6. (33:04) Search for VBA Code online: Add or count cells with red fill
7. (35:52) Form Control Button to Add Record to Existing Proper Data Set on different sheet.
8. (39:37) Summary

YouTube Channels to learn VBA:
Book by Bill “Mr Excel” Jelen: Excel 2016 VBA and Macros:

[maiang]

I have finished your Basics and Advanced Playlist today. Can't believe that I can get all of these for free. Thank you so much for such an amazing learning journey!!!!

[newdascene]

This guy is amazing, ive watched so many of his videos and now at work im consistently able to showcase my excel knowledge and currently am working on a big promotion based on my experience using excel. Couldnt be here without these videos. Thank you so much

[kiya]

The Best Excel Teacher as far as I know

Two years ago, I found out about Macro and I was thrilled to know and use it. I used it to create tools for easing my work, which me and my collegues saw as miraclous. But now I see this video, I learned a lot more. It answered some questions I had and as usual you've done an amazing job. Seeing you have thousands of videos on youtube, you should be celebrated.
Thank you

[abdulsamadibrahim]

I just finished all the videos for Advanced excel. I would like to say big thank you to Mr Mike!

[armondnazarian]

Done with the advanced Excel now! wow it's unbelievable how great and efficient you teach the Excel. Thanks for your hard work Mike.

[esanbifi]

I am 50 years old, in my life I’ve seen many teachers, honestly you are the BEST teacher.

[JTheill]

Brilliant stuff, this dude's talent for teaching excel is just something else.

[Cracktune]

dawg! you kill it! i bought your book Slaying Excel Dragons and boi; i got my sword ready. Thank you, man.

[hrprk]

Just finished the "beginners" playlist, (comprehensive for beginners but does not lack the quality and depth of an advanced course), and here to learn more. :)

[jkhoo]

Hi Mike, Just want to give you a big shout out for all your great excel tutorials. I am a fresh graduate who knew nuts about Excel and have been trying to learn from your vids to apply these concepts at work. They really helped me so much! Thank you!

[fjanjuah]

Dear Mike you made Excel so easy that it's understandable...You are one of the best tutors..Many many thanks...

[sasavienne]

No Excel without Mike. 👍 🌟 🌟

Thanks . 👍

[Javicrack]

Using the relative reference when recording macros is absolutely powerful !!! Thank you so much for this useful advice. Big fan of your videos !!!

[sim]

Whatever you teach in your video whether it is excel or VBA that turns out to be awesome for a learner like me or thousand others. It would be good for us to learn VBA from you as well. But so far I know you barely like to go through VBA coding. Anyways, don't get me wrong because whenever I learn anything from you I find that I can be a master of that very thing. You are the best. Long live sir!

[haythamamairah]

Thank you, Mike, this is a really amazing video!
But you can shorten the click of the Ctrl+Shift+Z keyboard shortcut from 14 times to just one click!
Try to update the code using the Do While Statement as follows:

Do While ActiveCell <> ""
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(1,
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

[sim]

Dear Sir, I've made a calculation using a macro which might pep up your venture a little bit. Watching your videos created on using relative references is always fun. Anyways, what I'm gonna say is that if you think of spreading your calculation to a table no matter how long and wide it is then I have already found one to share with you. I noticed that you have worked on the length side. Now, please record one macro applied for unlimited length and width.

Here are the steps I tried to impress you:

1. I selected the first cell then Ctrl+Right arrow to get to the right end of the table and then switching on relative reference button I moved to the next cell to write the heading then moved to the second row straight under the heading. Then switched the relative button off.

2. Here I used a sum calculation. Then copied it and after that, I pressed Ctrl+Home button to get to the first cell.

3. Then pressing Ctrl+Down arrow I got to the bottom of the table then ctrl+Right arrow to get to the right-most corner.
4. Then switched the relative reference button on and moved to the next cell up to which the calculation should be made. Then switched the relative button off.
5.Then pressing Ctrl+Shift+up arrow I reached the cell from where the formula I copied. Then pressing ctrl+V I pasted the formula throughout the cell I selected. Then pressed ctrl+Home tab to reached the first cell and ctrl+* to select the whole table to apply borders around it. And then I stopped recording.

I noticed that this macro is sufficient to spread calculation whatever the table length or width is.
Inspired by your three macro related videos I tried to make this. Hope you don't take it otherwise. You are the best teacher I've ever had in my whole life. Good day and T.C.

Md. Shahin Iqbal

[rodion-z]

You videos are the most amazing and useful. Thank you for spreading the knowledge (and fun!)

[Chamchijjige]

Ready to be data analyst now. Thank you very much.

[ashishmishra]

No one does it(excel) better like mike. He is my online guru thanks for your all tutorial "mike"(with absolute reference)🤗

[himanshudalai]

Thank you Mike for this awesome video !! You have made life easy for non-coders like me :)