Macros & VBA Training Series Part 1:
Writing Your First Macro & The Excel Object Model
Double-click video to view in Full Screen HD.
Download the file used in this video:
Macros & VBA Training Series
Enroll for FREE
The video above is the first in a 3-part training series. Can I send you the next two videos?
Please click the link below to enroll in the free training. I will send you video #2 in just a few days.
Please click the big green button above to register for the free training series.
About Me
Welcome to Excel Campus! I am excited you are here!
My name is Jon Acampora and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. When I'm not looking at spreadsheets, I get outdoors and surf. 🙂
What Process Do You Want To Automate in Excel?
Please leave a comment below with your answer, and any questions. Thanks!
very nice illustration, very clear very simple, thanks
I really liked the way it was explained so lucidly. thanks a lot
Very helpful, thank you!
I have data with 30 and 40 columns in 10 sheets.
I want to filter 1 column with one name and delete all others in all the 10 sheets
I understand easy cause your way of explaining is top class. looking forward to the next video
Hello
I have a spreadsheet that tracks tasks. There is a reporting process that passes from one person to the next. Once they complete their process they will change the drop-down from “No” to “Yes”. In the first column, I want to show who is the current owner of the process. The macro I can vaguely see is:
If column N says “No” then column A = column I.
If column N says “Yes” then column A stays blank.
Move on:
If column Q says “No” then column A = column I.
If column Q says “Yes” then column A stays blank.
Move on:
If column V says “No” then column A = column H.
If column V says “Yes” then column A stays blank.
And so on….
Great tutoring video. Thanks for making things so clear and easy to follow.
Trang
I would love to automate our month-end and quarter-end processes; they are repetitive, convoluted, and confusing.
To really understand how to use them
Hi Jon,
I have a big spreadsheet with tons columns. My team of 10 are using it everyday. Each members has different interest in different fields (columns) so we often have to filter the table by some categories and hidden some columns.
1. I want to create a button to clear the filter. I follow the exact steps in your video but the marco still not running. Dont’s know how to troubleshoot it.
2. I also wonder if we can display all and sort the table by the “start date” column.
3. I also want to create a marco that can do hiden and unhiden column and filter some categories to save my time every day.
4. I want to create a marco to set up the print area.
Are all possible with VBA?
Looking forward to attend your VBA course, but hope you can help me in the mean time.
Hi, this one’s a really good lesson to learn VBA as a beginner.
I want the data from another excel where the data is updated on a daily basis, what i the code I should be using in order to get this thing done?
I’m not sure what processes I’d like to automate yet. I’m trying to get a refresher on working with VBA. Your videos and presentation style are excellent — very clear and straightforward.
Good presentation – looking forward to the rest of the course
My compliments the way u explain process to automate this is the basic but i am looking for most complex which might take months or ~~~~?
any ready template ??
1) I want to automate PnL for options stocks & commodity markets buy/sell I trade only Options calls. puts, spreads, ratio spread,strangle
want to incorporate data from broker trading platform to independent xl. sheet as & when trades are complet which could be days,weeks, or months, Also like to create the graph for visual presentation, sure can be done !!
2) complete spreadsheet to run gas station + convenience store business, lottery,money order, beer,wine
Any ready templates free or reasonable price to put in immediate action .
kanu
Jon,
Very good overview, thank you for that. I have a question, using VBA how would one horizontally center a specific pivot row field without using the pivot select method? Is there a way?
Thanks so much.
I have many objects that I wish to automate both in excel and word.
The excel objects are based on a large dataset and my need is to set up a dashboard to allow management to see problems areas within a particular work process and pinpoint exactly where the problems are arising.
In word we have many set templates which should follow a standardised formatting directive, but the staff are constantly changing and the directive is not being followed causing additional work for the support team and delaying of the required outputs to senior management. I would like to use VBA to setup automatic formatting and data entry to speed up the process.
I have used VBA before, but a long time ago and so I need a refresher and learn about the new features that the MS applications have created to assist me in my quest to automate and streamline these processes.
Thank you for your videos, it has helped me to relearn some basic functions of excel and automate some tasks.
What I am struggling to automate in excel is the following.
The excel document has 29 columns with specific formulas in 10 of the cells.
There are also subtotal rows that tally up specific vendors totals before moving onto the next vendor and their inputs.
There is a grand total row as well.
What I am trying to do is create a macro that will enable a user to create a new vendor line above the grand total row. This new vendor line will need to have 10 rows + 1 subtotal row with formulas that will update and reference the correct new cells. The Grand total row will need to sum the newly added Subtotal row with the previous subtotals rows.
The second part is that the subtotal row needs to then create a new row in sheet 2 (Profits & Loss) with the data from the subtotal row.
How would I go about creating this automating this in excel?
Thank you.
Very informativeand comprehensive video. I have never watched a video that clearly explaining scary codes. Thanks to this I am now looking forward on how to to automate things.
Even if I am a beginner, I now have tasks I want to automate. Some of these are: records reconciliation, autocompilation of data based on the same form of input, and of course autogeneration of data from a set of information. These may sound complicated to me now and all but will try my best to get there even if I am not really a coding-type of person. In all honesty, I really am amassing so much courage to learn MACRO and rrlearn VBA becausr I hated it before. I really hope I find all guidance I can find through excel campus.
I did step 2 the same way and 2nd line up nothing? Should I be replacing something on the 2nd one?
——————————-
Sub Macro1()
‘
Macro1 Macro
‘ Run Opitimzer
‘
X = SolverSolve(True)
End Sub
———————————————————————
Sub Macro4()
‘
Macro4 Macro
‘ Clear Optimizer
‘
Range(“I2”).Select
ActiveCell.FormulaR1C1 = “0”
Selection.AutoFill Destination:=Range(“I2:I201”)
Range(“I2:I201”).Select
End Sub
—————————————————————————————————————————
Sub Macro5()
‘
Macro5 Macro
‘ Mult2
‘
X = SolverSolve(True)
End Sub
——————————————–
Sub Macro6()
‘
Macro6 Macro
‘ Clear Optimizers
‘
Range(“I2”).Select
ActiveCell.FormulaR1C1 = “0”
Selection.AutoFill Destination:=Range(“I2:I201”)
Range(“I2:I201”).Select
End Sub
Can’t seem to get to Video 2 ?
How do I in Excel or with VBA take data from excel and put it into Word?
Say I have to send a Weekly SITREP of my progress that is tracked in Excel and then write a Word document explaining the details…. I’d love to be able to have a Word template that uses the Excel data (tables, cells, etc) automatically instead of Copy/Paste into Word…. and then send the Word doc via email to the boss. =)
I would like to automate my KPI’s, or Key Performance Indicators. I receive data several times a week from my company that spans 20-30 data properties albeit in an already clean Excel format. I want to be able to filter this data automatically and update a homemade KPI Data Table with ease. Sort of like feeding my company’s data into my table and my table pulling the data I need it to accurately every time.
I’m using the newest version of XL which I assume is 2019. My insert does not yield a module choice. I do not see the button you referred to where I can add a macro module VBA module. In 3rd the keyboard shortcut of alternate I with the letter and asked me to continue the code from an earlier version of XL.
I found video very useful, i have been wanting to learn macros for so long now and this video helped me to understand the basic rules.
I want to be able to pull data from several spreadsheets and put the data into one spreadsheet as a table format and chart.
I would like to have pivot tables update automatically with changes in the source data. I would also like to have the updated pivot tables retain or reapply any conditional formatting.
I need to automate the journal entry and reporting process of daily accounts payable and receivable procedure of my firm. It involves daily, monthly, quarterly and yearly summary reports to be handled with. I don’t have much time to teach my sub-ordinates but giving them a good grasp of automating the process with Excel will easy them to work much better.
Assem Ibrahim
our factory has customers that get goods from the warehouse on daily basis so i want to automate the process of transferring the amount released goods to be sent to each customer ledger account.
it is my first step in learning how to automate my work instead of using excel functions.
Pl send any resources that can help me to START FROM (0).
I want to automate the return of a formula in a cell based on some changes in other cells. With “If” function I can set the value of a cell but cant put a function in a cell.
HI..
very nice and useful page you have…
please i want
when cell k1=1
copy cells C1:cC20 and paste them (special form) numbers to A1:A20 .
and when
cell k2=1 C1:cC20 and paste them (special form) numbers to A1:A20
and when
cell k3 = 1 C1:cC20 and paste them (special form) numbers to A1:A20
and when
cell k4 = 1 C1:cC20 and paste them (special form) numbers to A1:A20
and when
cell k5 = 1 C1:cC20 and paste them (special form) numbers to A1:A20
thanks in advance..
I’m a 77-year-old who has tried to learn at least one thing every day of my life. I thought to learn just something about Macros would be fun.
I have written a code in which a macro is run depending on what was selected in a drop down list for a cell. Then I need this same macro to run on multiple cells the same sheet and also on all 27 sheets of the workbook. I know there has to be a way to have this done without doing a all those macros for each cell in all the pages. Any advise would be greatly appreciated
Hi Jon
I like to know if there is any way to extract information from cell’s comments and paste the information in another cell.
I am a blind guy who uses a screen reader to navigate the PC. Thank you so very much for always providing keystrokes for various processes that you cover. I can’t use a mouse so the keyboard shortcuts are golden.
Never really understood the object model details till now, even though I have been coding ! thank you
Below is my code for My First Macro … it completes everything but the application code line …never prints Goodbye on the sheet at A1. Can’t see anything wrong …. could you please check this and let me know what I’ve done incorrectly. Also F8 doesn’t walk through the code lines ..have to go to menu each step.
I am using excel 2010….Thanks in advance for your help.
Steve
***************************
Sub First_Macro()
Worksheets(“Sheet2”).Select
Range(“A3”).Select
Range(“A1”).Value = “Hello”
Range(“a2”).Value = 100
Application.Workbooks(“My First Macro.xlsm”).Worksheets(“Sheet1”).Range(“A1”).Value = “Goodbye”
End Sub
Hi Steve, if I may reply to this comment, I believe your value Goodbye had appeared in the Sheet1 instead of Sheet 2. You may have seen the wrong sheet?
And the F8 issue that you mentioned may be due to the type of keyboard. Certain keyboards require you to click the Fn key on our keyboard then F8.
Apologies if this didn’t help you out.
Great video thank you. The process I am wanting to automate is that I have the weekly takings report (with a tab for each day of the week} then I need to compare it to last years figures on a weekly summary report Currently I am copying and pasting between workbooks but it would be fantastic if I could learn how to autmate this process
when Jon added ++ sheets to his workbook – I ran my macro written earlier on a video “Add_Three_Sheets”
i’m so happy i’m getting it. Jon you’re a great trainer. this site means so much to me. it will keep my job and increase my value.
I have many costing reports for packaging manufacturer, these are on going. payroll commissions, each being handled differently. I know I have a long way to go in learning, but I’ve begun. and today i’m going in to work on KPI – the old way for now
Very basic, greatly explained, good easy to listen to voice, keep it up. Thank you.
Hi Jon, I wanna copy and paste the part cells to another sheet; how can i use the command for change row?
please
I want to take a whole row that includes a macro adding a row below it, as well as a macro that deletes that row, and a list of formulas in the cells
IE:
+ – a b =a+b
I would like combine multiple similarly formatted sheets into one master sheet in same workbook. For example, I have 12 sheets named for each month. Each sheet is formatted the same column headings and contains that month’s daily transactions. The master sheet would append all the sheets data together. How would I automate that in a macro?
I have more than 20 excel workbooks in a folder. I want to make a master workbook. I don’t want all rows and coloumns I only wants some cells value to the master workbook.How can I do this. Please
Your video was very clear and made it easier to understand some of the basics of VBA/macros – objects, properties, methods etc. Very impressive! Looking forward to seeing video 2
I have two sheets, I want to compare the data and categorize them as per my requirements.
I need to append two sheets and then make a pivot table.
Sir, I love you. You are genius. Very useful macros you are sharing in your website and YouTube.
Thank you Lenin! I really appreciate your kind words and your support.
And I want to thank you too, sir. I’ve been trying to learn Macro VBA for years and your video is the only one that gave me that “a-ha”moment. Because of it, I can finally automated my work and save so much time. Thank you very much. May God bless you more for making it free.
I planned to enter all daily transaction including sales, purchase, receipt, payment, bank deposit, withdrawn, drawings both by cash and from bank. Cash received often as petty cash from owner ( we kept apart from capital as Mr. X Current Account).
All above in workbook 1.
Workbook 2.
Customer wise ledger. Sheet x
Followed by age wise analysis (0-30 days, 30-60 days) etc.,
The question is this table must deducted the receipt against sales bill and balance should be displayed under age group analysis.
Will you please help me.
Currently I created using recorded macro.
Trying to add automation to a consolidation of information from multiple sheets into one by using Excel VBA on a Mac. Thank you, Jon, for the videos. I learned VBA 20 years ago but haven’t coded in years and need the refresher.