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!
Easy to follow. Looking forward to next video. Would like to automate hiding/unhiding blank rows and columns. Would like to automate monthly report printing.
I want to automate matching lines to combine them. for example, on a bank reconciliation, I want my macro to look at two particular data columns for a match, then consolidate the lines. Love Andy!
New to VBA & Macros. I have data in several cells on a work sheet that move based on the =Today() function and I want to be able to move that same cells info to another area of the same sheet so as it disappears via the today date function the data is still there.
I am responsible for maintaining budgets at home and at work. I’d love an easier way to do that.
As a teacher I have say 60 students and I need to list :-
1. Each student as to Grade (eg 50 should return the alphabet C in the neighboring cell.(A, B,C etc)
2. A summary as to how many students gets A, and how many got B etc.
3. And a column graph as to how many students got A, B, C etc.
TQ
i want to upload the contents of my excel sheet into a website, one cell at a time.
I need to do that as well. I need to copy from 70 different forms or worksheets, the same 50 cells over to a web form written in PHP.
I would like to add columns to a spreadsheet that is updated monthly. Currently I hide the oldest, copy and insert the newest, delete contents of the copied column and paste in the new information. There are 16 metrics side by side and the same task must be done for each.
I really interested in learning more about excel all about excel, and I’m so glad to fine your website, I’ve been struggling in learning some about excel that I can’t understand. I’m creating some tamplate and I want it to automatically synchronize with other Excel n work sheet but I still lack in knowledge of excel program. Hope you can advise me to solve my problems.
Thank you
Hi jon,
As a finance professional, i would like to automate few tasks like hide gridlines, copy the company name as heading to every sheet etc.. these are repetitive in nature and consumes lot of time.
I wish to gain skills to build from,scratch, a robust and usable Excel based Desktop application to be used by others without any excel skills
I want to change a cell based on user entry to a cell. The problem I have is that I’ve written the VBA code to use the Change event but Excel asks for the name of the macro. I thought the code would just execute. How do I tell Excel I want to use the module that I’ve created? Thanks. I really like your videos.
Fantastic! Jon.
I have multiple rows and multiple columns and would want to concatenate the cells on each row. E.g. concatenate values in column A, B, C of row 1 and put in cell R1D and do similar for subsequent ones
A B C D
R1 2 5 10 2.5.10
R2 1 3 8 1.3.8
R3 3 2 8 3.2.8
The number of rows varies depending on information collected. I trust you would assist
Over the last years I have used Excel and Access for my work and wrote a lot of VBA code in the process. Sadly the firm where I worked went bankrupt and I lost that job. I have found a new one for the time beeing but I would like to show of my skills by means of a certificate and I hope to reach that goal with this on line course.
Its my first time in vba world, and the first video help me to understand the base of vba.
looking forward to the next video.
hi,
i handle the budget preparation of my company; i find it difficulty to consolidate various sheets and establish the link thereon..
hope you can assist me on that.
thanks a lot..
//dave
I am interested in automating the addition of a worksheet to a spreadsheet that contains not only static data fields and formulas, but also has a number named ranges and other references some of which are constructed through concatenation of a portion of the name of the new worksheet.
This would be a good place to start.
Right now I just manually create a copy of the prior months worksheet, and then I need to manually change the new sheet name and then the names ranges in the Name Manager.
The video is really very useful . I haven’t used excel VBA before & I am not a professional at using excel , but I am supposed to make optimization & I found it easier to implement in excel than Matlab .
But , excel solver add ins only provide optimization using 3 methods : Simplex LP , GRG non linear & Evolutionary GA .
But , I need to implement optimization using other algorithms that are not found in excel solver add ins like ACO Ant colony & PSO particle swarm optimization methods & I found that I can get their excel VBA code & use them in excel &
I don’t know how to do this … so any help please concerning this subject ??
Appreciate your feedback .
Thank you very much .
to be able to create a macro to expedite processing huge amount of data
I have a list of 100’s of suppliers (refreshed monthly to ensure new accounts are added) – (Column A = Supplier code & column B Supplier Name) AND I have a monthly Supplier Reconciliation Template that comprises basic sub & totalled areas [Supplier balance, less items under query or missing doc’s, credit notes, outstanding payments and items actually to be paid, resulting in reconciled Bal per Supplier a/c ledger], with opening balance per supplier statement and closing balance of payment due for the month as per my Supplier account ledger, which supplier code, name & total due to be paid must be carried into a summary sheet and sorted alphabetically referenced to the specific month.
Required:- to create monthly, worksheets only for relevant suppliers this month concerned and point the payable balance to a summary sheet saving time/effort manually effecting the time consuming creation of a Suppliers Control by selecting a macro button whenever a new supplier recon is required!
The sequence needed is to search the name of suppliers within the supplier list, find the relevant supplier name (considering duplication in similar names)by using an ‘input query’ into the macro, then creating a new worksheet by copying the template worksheet and renaming this worksheet with the suppliers name as found in search above, renaming worksheet, then filling in the supplier name and code into the relevant fields in the template and finally creating a reference to this worksheet by supplier name & code in the Summary Sheet and pointing the payable reconciled balance per template to the Summary sheet, which Summary is totalled giving the value payable for all suppliers for that period.
The specific clerical data can then be entered into the newly created reconciliation template and confirmed and a running total of payment due within cash-flow budget can be seen at all times as in e.g. a Dashboard effect as well as a summary list for print & record.
Great Thanks – Clears many basic issues I was not aware of!
Would like to automate the priorities of my tasks
I have for three different Projects
I have three worksheets (Each WORKSHEET contains my tasks for each on of my Project)
Each Task is a line item with columns indicating info of the task, and mainly the Priority, Category and various dates
I want to extract the Task which has Priority “A” to a new Sheet called “A”, and extract the Task which has Priority “B” to a new Sheet called “B”, etc. for all my Priorities up to “G”
The Columns headers / titles need to be on the new Priority Worksheet as well
This must happen for Project 1,2 and 3 (Therefor I will have all the priority “A” Task line items from my three different Projects in one Worksheet for ease of getting through my priorities.
Daily I will return to each Project Worksheet and either delete the task when completed or alter its Priority code – Once I have run through all the projects, I want to run the Macro, then I have my Priority types all in one spot !
I trust this makes sense?
Thanks and regards
Martin
Great lesson. Clearly explained and demonstrated. You have me from now on as one of your fans. Thanks
Need to automate several reports from work. This is really helpful.
Nicely Explained and the best video. Thank u v much Jon
Great Video, Jon!
I got to learn the basics and your videos help me save time at my work!
Please continue to post such videos.
Great intro course
Great very easy to follow looking forward to the next video… Thanks.
Excelent video ! There couldn’t be a more complete, concise and engaging Excel Macro Primer.
This is a goto site on my Bookmark, and I will surely visit each week.
Thanks !
This great.. thanks
I have been using Excel for a long time but never tried to learn how to write macros. I am developing a Excel application and need to automate a process. I will be great to learn VBA so I can write my own macros.
Fantastic well explained. Wish you too were Word geek because I have some bookmarks I’m going to copy into an excel document. I’ve seen one of your other videos where you use find the last row … etc.
Do you have a suggestion on how it can be done?
I would like to create a macro that will insert formulas such as MIN() in blank cells across a number of columns when there is no set pattern to the blank rows. Eg. Row 3 is blank then Row 7 is blank and then row 16 is blank.
Thanks for your help
Mark
Thanks and I just start learning Macro.
Hi,
What I would like to automate in my workbook is the ability to navigate the user to specific cells to enter data, not let the user move to next cell until they enter data in this specific cell (have a message pop up explaining they need to complete data before moving on) and finally, navigate the user over cells that I do not want them to populate.
HI, I have worksheet with (In Header) Name of Share, Date of Purchase, Rate, Amount, Brokerage, Date of Sell etc. I want Macro to 1) segregate shares by company Name in Separate Sheet.
2) Do the calculation of Profit/Loss in terms of Days of Holding and Annual Profit/Loss i.e annualised %age return & holding return period.
3) No of shares of a particular company held as on date and on a particular date.
4) No of shares of a particular company held as on date and on a particular date in my name, my wife name, my child name.
5) No of shares of a particular company held in separate Bank as on date and on a particular date in my name, my wife name, my child name.
IT WOULD BE GREAT GREAT GREAT HELP TO ME.
Nice intro video.
I have an excel sheet that has some part numbers and pricing. However the prices are offset down one cell, what I want is to bring the prices up on the same row as the part numbers and get rid of the now blank row that they were originally in
So I have this:
47-5477
669.99 EA
47-5479
651.49 EA
47-5271
50.99 EA
47-5275
47.49 EA
I want this:
47-5477 669.99 EA
47-5479 651.49 EA
47-5271 50.99 EA
47-5275 47.49 EA
Hello Jon,
Saw your first video on you tube about creating pivot charts. Very helpful in a task I was assigned by Management. I will review them again and apply so that it is second nature. Really enjoying the newsletter. The video “Intro to Macros & VBA Part 1 is a really useful first step for me. Thank you so much! Once I have completed the series of video’s I will have a better idea of the functions I can automate here at work.
I want to lmport my checking account transactions into a worksheet every month. I am aware that everyone has made a.worksheet to do this manually but I want to learn how to do this for accessing other databases like MLB team and player statistics. When I tried to get my account data I assumed that the bank would block a software request if I didn’t provide password etc. I didn’t try. I manually got to the information in a screen since there seemed to be a button to create a link.
This brought up a box saying something about JavaScript was needed to do this process. I do not want to learn a lenguage except VBA in the near future.
Can you suggest what I can do using just Excel?
I am a complete novice. I was a helpful starting point.
I have to adjust Inventories every week. I use formulas that link the Inventory total spread with the new Inventory filed every week, i.e. Inventory A, December 17, 2017, Inventory A, December 24, 2017, Inventory A, December 31, 2017 etc. So the Inventory Total spread is Linked to December 17 first then December 24 etcetera. Once I build the formula for Inventory A it is easy to edit the links and switch to the next week’s filed Inventory totals. What I would like is a Macro to build the formula for me. I built the Links for Inventory A but there is almost 200 Lines of products and each cell for the Total Inventory has to be linked to the proper cell for the Weekly Filed New total and the inventory totals change every week. The real ISSUE is that I built the one for Inventory A but that is only one of 26 Inventory/Plants. This requires that I build linkages for Inventory B, Inventory C, etcetera all the way to Inventory Z, all containing over 200 separate links within each Inventory Total. Rather than build 26 separate Programs that can link 26 separate Inventory Total Spreads is it possible to design a Marco to build the Linkages for the 26 separate Inventory Total spreads?
Michael
please send formula how to integrate?
Thank you Jon. Great Video, very neat, explanations are easy to follow and graphics make the experience enjoyable.
I have a razor that is connected to 3 Pivot tables and I have managed to create a Macro to save the report as PDF for each item in the filter with name of file joining information in 2 cells. However, I have 2 issues:
1. I have to run the Macro for each item in the filter so it is a very manual exercise so I need to enhance it to create a loop that runs all items in the razor
2. I have added a dynamic printing layout to my report since the size of the pivot table changes for each item in the filter but since these are 3 different Pivot tables, when I run the Macro, it saves blank pages when one of the pivots is larger than one page. Therefore, I also have to add something in my macro to delete blank pages when saving.
Thank you
Best Regards
Ricardo
I just finished watching the first Macro and VBA video.
Jon, I am a 78 year old retired teacher, and you are what I would classify as an excellent teacher!
1) You obviously know you subject matter very well,
2) are very easy to follow,
3) use excellent simple techniques to demonstrate the idea you are trying to get across and
4) it is all done with excellent clarity.
But, perhaps more important than the above points, you obviously want to help people to learn and understand and you clearly love and enjoy doing it.
I have subscribed to your channel on YouTube. I look forward to a lot of good, useful and enjoyable learning.
Thank you very much,
Howard D Keney
Thank you so much for the nice feedback Howard! You made my day! 🙂
And yes, I love to teach and help others learn Excel. My mom was a 2nd grade teacher, so that must be where I get it from… 🙂
Thanks again for all your support, and have a great day! 🙂
Hi Jon,
Thank you for sharing your time and expertise.
I work with numbers and have worksheets that contain 1 – 8 columns of numeric figures that I 1) need to copy & paste special values from one workbook onto another. The lines of data I’m copying from vary in locations; the figures are listed in columns with/without headers and may be separated on multiple columns and worksheets. 2) I just want to copy the numeric values without the header from each column onto a template that I created. I’d like for the copied data to go from column to column, not row to row. 3) I want to save the template file as its own, new file.
Additionally, I hope you can go over looping. I can’t wait to see your tutorial on user forms.
Thank you in advance, JC.
what I want to automate is taking a cell value from Sheet1 and using the cell value to name another sheet in the same workbook
Awesome. I love how you stick to the point and don’t ramble on and how well you explain the material. I’ve been programming in VBA for years but never had formal training and have to do a trial and error approach using pieces of code I find off the web whenever I get stuck. Most of my projects have involved obtaining data from various web sites (mostly stock data) and then performing various analyses within Excel. With your training I hope to improve my overall skills.
Thanks!
Nice Job!
Very simplified steps. Nice!
Thank you Great Video to kick start VBA..!!
HI,
I have written code to filter a data in my report. Now want to write a code to copy the filtered data in another worksheet.
Please help.
I want to set a cell with the result of a formula (vlookup to another worksheet) as a value which is a date.
Based on how long ago that date is in months (i.e. if more than 12 months ago), I want to copy the entire row to another workbook (maintaining the date as a value) and then delete the row.
Hi Jon,
I need to prepare sales reports each day and mail them to several responsible. The process is like
1. Data download
2. Cleansing
3. Adding certain attributes using v-lookups
4. Using Pivots for dashboards
5. Mailing individual data to several responsible for sales apart from that to the senior management.
Can I get some help on this? specially the mailing part?
Best wishes…