VBA Training Series – How to Write Your First Macro (Part 1 of 3)

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:

My First Macro.xslm (93.0 KB)

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.​

Video 1

Intro to VBA:
Writing Your First Macro

Video 2

Automating Tasks:
The Summary Report

Video 3

Userforms:
The Exports Sheets App

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!​

1,548 comments

1 2 3 21

Your email address will not be published. Required fields are marked *

  • Hi – thank you for the excellent intro! I am looking forward to the next part of the series.

    We are trying to augment our data warehouse fire hose of information down to consumable dashboards in Excel. To address a gap we have with our data, which is versioning, we take snapshots of specific datasets every week which is then used in a dashboard for managing progress. In addition to that, and this is where I need to use VBA, I need to compare each week’s snapshot line by line by a key field and then compare what has changed and then report on those changes. Thoughts on the best way to do this?

    • Thanks Alice! That is a great question, and I process I deal with a lot. I do the same thing for snapshots of sales pipeline every week. This can sometimes be done with formulas or even pivot tables since you do have a unique key field in your data set. Here is an example of a possible solution with pivot tables. It isn’t going to give you all the answers, but you can see the basic concept.

      https://www.excelcampus.com/pivot-tables/compare-lists-with-pivot-table/

      If you want more of an automated one-click solution, then VBA is a good choice. I’m also experimenting with doing this in Power Query. As you can see, there are a lot of options and probably no perfect solution. Please let me know if you have any other questions. Thanks again!

  • Thanks Jon. Very nice explanations.

    Im tax experts and maybe this will be helpful to do my job.

    I looking foward for the next video

  • Jon!

    The video was great! It was super easy to follow. Much, MUCH easier to see it in action and step-by-step than to chug through a ton of reading.

    I work with Excel all the time, however, it’s not from a financial aspect. I believe this is why I don’t often understand some of the examples, because they don’t apply to how I use the workbooks.

    Most of the time I am taking different sources of information and putting them together to create a ‘Gold’ source of data.

    Example:
    Just like your hierarchy for the workbook:
    Racks -> patch panels in the racks -> ports on the patch panel -> connected to a switch -> switch ports

    Or having to cross-reference things.

    To answer your question about what I would be interested in automating, I suppose my focus would be on page set-ups of data so that it is all consistent [caps where it should be caps, or proper, etc .. the cell/font sizes, page/tab names … things like that …]

    That’s what comes to mind first the tedious stuff …

    Thank you again for the wonderful video ..!
    —-
    Jae

    • Hi Jae, Thanks for the comment! I’m happy to hear you enjoyed the video. That is definitely all work that VBA could handle. Life can be a lot easier (less frustrating) if you can just get some of the tedious stuff out of the way. That’s what I love about VBA. Hopefully the next few videos will answer more of your questions. Thanks again Jae!

    • Hi Raja, Great question. Here is a simple macro that will loop through all the sheets in the workbook and change the zoom. The Zoom is a property of the ActiveWindow property.

      Sub Change_Zoom()
      Dim ws As Worksheet
      
      
          For Each ws In ActiveWorkbook.Worksheets
              ws.Select
              ActiveWindow.Zoom = 90
          Next ws
      
      End Sub

      If you wanted to do this when you open new workbooks, you would need to create an add-in that captured the event of opening a workbook and firing the macro. That is a little more advanced. What you could do is add this code to your personal macro workbook, then put a button on the ribbon that fires the code. I hope that helps. Thanks again!

      • Thank you Jon. I actually wanted to fire the macro every time i open a workbook. Since that is bit advanced, I am satisfied with this for now. But one more quick small question, It stops at the 3rd sheet. How do I make my cursor return back to Cell A1 in sheet1 or to last non-blank cell in the work book?
        Thank you. I love its working though. Thank you again Jon
        Raja

  • Hi Jon,

    This is a great video and great introduction.

    I work in accounting and often pull down revenue/expense journal entries to excel to create reports. When I do this I have to manually write a formula to assign whether the amounts in the entry are positive or negative depending on whether the value is a debit or credit.

    I wonder if there is a way to write a macro that I could run to do this rather than having to manually add the column and formula each time.

    Thanks again and I look forward to watching the next videos!

    • Thanks Travis! Yes, it’s definitely possible with VBA. It depends on how your spreadsheet is setup and what is flagging each entry/row as a debit or credit. You will probably need to loop through the cells to determine if it is a debit or credit, then change the value. To change the value with VBA you can just multiple the existing value of the cell by negative 1. The following code would reverse the sign for the value in the active cell.

      ActiveCell.Value = ActiveCell.Value * -1

      This would just have to be modified to use in a loop and loop through all the cells that contain debit or credit values. We talk more about loops in the next video. Let me know if you have any questions. Thanks again!

  • This video very informative and easy to follow. I look forward to the next weeks since there’s a project am working on that will lend itself beautifully to having macros.

    Again, great presentation!

  • I haven’t used VBA in 10 years and this did such a good job of explaining the fundamentals and terminology! Thank you!

  • Hello Jon,
    It’s a progressive and well done “tuto”, thanks.
    I’m a beginner, it’s perfect 😉
    My aim is to create a macro :
    First The context :
    . a sheet in a workbook with a big data base, I will call it A
    . several other workbooks with the same type of data base, I will call them B, C, D
    Secondly The aim :
    The data base from the sheets B, C, D have to be copied then pasted in the big data base in A, (datas have to be gathered together in data base A)
    As I’m french, I don’t know if my english let you understand what I want.
    If I understand, a next video will do that, isn’it ?
    Thanks again ! 🙂
    Liana

  • Hi, I liked this basic video. It really helped me learn the fundamentals of VBA.
    Thanks. Hope to see the videos 2 and 3 the soonest.

    I liked the clarity of your voice, the clarity of your presentation, how you define everything.
    I also liked how the video was presented. Everything!

  • Hi Jon,
    Thank you very much for creating such a good tutorial.
    I had gone through your tutorial on “Creating Dash Boards using Pivot Table”. It was so good.
    I used the same to prepare the monthly, Quarterly report. Believe me I got good appreciation from my colleagues, Dept. Heads and CEO! Credit goes to you!

    I want to automate the process of creating the Weekly, Monthly and Quarterly reports of my dept. I also wish to train my team of 5 members on organizing the data to make the best use of Excel features and save a lot of time.
    I hope you will surely solve my problem.

    Thanks again,
    Dheerendra Bilagi

    • Hi Dheerendra,
      That’s so great to hear! I’m glad you were able to put the dashboard lessons to practice and impress your colleagues. That’s awesome! High five! 🙂 They will really appreciate you automating those reports as well. VBA is one skill that you will find yourself using for a lot of projects.

      Thanks again for the comment and have a great day!

  • I would like to be able to automate a drop down lists that are dependent on each other. For example choosing an option from the 1st list gives you certain options in the second list. But if the option is changed I want it to make sure it clears the option selected in the second drop down list and make you re-choose.

    • Hi Thomas,
      Great question! That is definitely possible with VBA. There are a few options for this when you use VBA. You can use form control comboboxes that sit on top of the spreadsheet. These can be inserted from the developer tab of the ribbon. Those controls can be programmed with VBA.

      Another option is to create the lists inside of cells using validation lists. Here is an article on how to do that, and it also contains links to alternative methods.

      https://www.excelcampus.com/tables/dependent-drop-lists/

      Let me know if you have any other questions about it. Thanks!

  • Hey Jon-

    Great presentation ! Kept my attention throughout. Very engaging (and entertaining at the same time) especially with all the pictures, pop-ups, effects, etc… which help made it less intimidating and just easy to follow.

    Can’t wait for the next videos to trigger a “light bulb” moment for me as to how I can use it in my world. This definitely whet my appetite.

    Thanks
    Dennis

    • Hi Dennis,
      Thanks for the awesome feedback! I’m so happy to hear that you enjoyed the videos. VBA can be intimidating to learn at first. It’s best to take it slow and practice as much as you can.
      We will start to automate processes in the next two videos, and I think you will see how much time VBA can save you. Thanks again Dennis! 🙂

  • This is a dream come true.

    Thank you for putting me on the right path to master excel.

    You are great.

    Thanks a lot

  • Dear Jon,
    Thank you for explaining to me the difference , now I know the purpose and the benefits of VBA and Macro, I will watch the video one more to be expert in it till the second video , cause it will help me so much in my work .
    I would like to ask if there are other videos for Advance Excel that I can watch . I already subscribe in YouTube Excel compus channel and I learn so much from it .

    Really Thank you for your assistance ..and God bless you

    Best Regards

  • Hi John,
    Thank you for this video , but I a little bit confused for the difference between macro and VBA , and when I can use them … I have to deal with Budgeting reports, and how it can be easier for me to update my reports .

    Thanks

    • Hi Farouk,

      VBA (Visual Basic for Applications) is the name of the programming language used to write macros. The macros are the programs we can write to automate a task. Once you write a macro, you can then run the code to perform all the actions in Excel.

      I hope that helps. Please let me know if you have any other questions. Thanks!

  • Thank you for this very informative presentation of Objects, Properties, Methods and the Object hierarchy. It makes what seemed like an impossibly complex subject more manageable.

    • Thanks RJ! VBA can definitely be overwhelming at first. I think that once you get a good understanding of the basics, and practice the techniques, it becomes a lot easier to automate processes. Thanks again and have a good one!

  • Nice video. I am a total beginner in VBA, would love to be able to use it to help me with repetitive data manipulation (monthly, quarterly report of data and so on).
    I still don’t grasp how I can share my macros in a group of files.
    Every time I extract data from my databases I create a new file (say “SamplesMarch15”; “SamplesApril15” and so on) which I then have to manipulate in basically the same way. How do I incorporate an existing macro there? Besides Copying and Pasting of course.
    Thanks a lot for the great work.

    • Thanks Daniela! That is a really great question! We can use Add-ins to “share” macros across files. The Excel Add-in is basically installed on the users computer, and they can then use it to run code on any open file. You can run the code by adding custom buttons on the Excel ribbon or right-click menus. This means that the code does NOT travel with the workbook. An add-in file is still an Excel file, and just saved as a different file type (.xlam).

      Another alternative is to write a macro that copies the code into your monthly files. You would use this if you wanted the code to travel with the workbook.

      I explain all this and go into step-by-step detail on how to create add-ins in my upcoming VBA Pro Course. I will send you an email when it is available.

      Thanks again and have a great day Daniela!

  • Jon,

    Great video, I’m looking forward the next ones.

    I was trying to learn macros on my own, and needed to select some cells, which I never did figure out.
    Some of the methods I would like to learn is selecting multiple ranges on a WS and printing them.

    Also, making reports and printing them.
    For me, this is just the beginning.

    Thanks,

    Jeff

    • Thanks Jeff! Here is the code to select multiple ranges.

      Range(“A1:B2,D2:D5”).Select

      You just separate the ranges with commas.

      The Range object also has an Areas property which will tell you how many non-congruent areas are selected. You can loop through the areas collection to return the selected ranges. This is probably more than you will need for your process. I remember when I learned about the Areas property and it has come in really handy, so I thought I should share. 🙂

      Thanks again!

  • Thanks for a great intro to VBA. I got more out of this one tutorial than the first 40 pages of a VBA book I purchased last year. And the use of kitchen appliances/tools is very helpful because they easy to visualize. Well done.

    But the kitchen tool analogy confused me in one sense regarding Properties and Methods (Actions). Perhaps I am focusing on the physical, but at 6:40 you classify ‘peeling the banana’ as a change to the bananas property. Wouldn’t that be a Method (action), since peeling is an “act”?

    Thanks for any clarification.

    Regards,
    Bob

    • Hi Bob,

      Thanks for the comment. I really appreciate your kind words. That is a great question about the property of the banana! You are right in that peeling the banana could be a method (action).

      If the peel was a property of the banana, then the existence of the peel could be a state of that property. The peel could be on/off. Similar to a light switch being on or off.

      This can get confusing in VBA as well. Sometimes we change properties of objects, and that change feels like it should be an action (method). For example, changing a worksheet name in Excel might feel like we are taking an action to change the sheet name. However, we just need to set the Name property of the worksheet to a different name.

      The following line of code sets the Name property of sheet one to change the sheet name.

      Worksheets(“Sheet1”).Name = “Summary Report”

      This might make more sense than the banana analogy. The physical action of peeling the banana could definitely be considered a method.

      I think it’s best when learning VBA to check if there is a property that can be manipulated before trying to take an action with a method. You can tell the difference between a property and method by the icon in the intellisense dropdown, as I mention at the 8:20 mark in the video.

      I hope that helps. Please let me know if you have any other questions. Thanks again Bob!

    • Thanks Anil! I have used VBA a lot to automate headcount. Depending on your source, headcount data often requires a lot of scrubbing, lookups, merging, etc.

      Checkout my comment above to Ed on Excel Tables. They are great way to manage your data and manipulate it with VBA.

      Power Query is another tool that can help with this type of data. With the combination of VBA and Power Query we can build some great automated systems that will save a lot of time with headcount work.

      Thanks again and have a good one!

  • Clearly explained and simplified, cant wait for the next video. I want to know more about dynamic ranges since i’m dealing with data that constantly updating. I’ve seen your videos on dashboards and pivot tables and it helps me a lot.
    Thanks a lot Jon!

    • Hi Ed,
      Great suggestion. There are quite a few ways to deal with dynamic ranges. My preferred method with VBA is by using Excel Tables.

      Tables are great because they automatically expand as the user enters data, and they are easy to reference. In VBA, Tables are referred to as ListObjects. ListObjects have a ton of properties and methods to work with the Tables. I have an entire module dedicated to Tables and ListObjects in my upcoming VBA Pro Course.

      You can also reference Tables with their structured referencing in the Range property. The following line of code would select Column1 in Table1.

      Range("Table1[Column1]").Select

      Tables are great, but I realize they can’t be used for every situation. If you are using dynamic named ranges then you can reference those in the Range property as well.

      Range("MyNamedRange").Select

      If you need VBA to create the dynamic range on the fly, then you will want to find the last used cell in a range or on a worksheet. I wrote an entire article, plus videos, on this a few weeks ago. Here is the link.

      VBA Tutorial: Find the Last Row, Column, or Cell on a Sheet

      I hope that helps. 🙂 Thanks again Ed!

  • Great video, thanks. Will parts 2 & 3 be available a bit longer when they’re released – it’s holiday time and I’d hate to miss them:-)

    • Hi Cath,
      Great question! Yes, you will be able to access these pages for as long as you like. I will be sending you emails that contain links to parts 2 & 3. Just keep those emails and you can click the links to access the pages anytime in the future. Please let me know if you have any other questions. I hope you have a great holiday! Thanks! 🙂

    • Great question. I discuss variables in video #2. I also have a 3 videos dedicated to declaring and using modules in my upcoming VBA Pro Course. Variables are extremely useful and help make your code more efficient.
      Thanks again Shivaji!

  • Hi Jon,

    I will be awaiting for your next videos on this series. Please kindly Upload them as earlier possible.

    Thanks Jon
    Shivaji Upase

  • Hello, Jon

    Really This is very good stuff. The clarification on Excel object Model including how to write first macro
    is excellent. The stuff which you have provided is useful for non vba programmer also.

    Thanks Jon
    Shivaji Upase

  • Hi Jon,
    Excellent video, good and clear explanations. It’s always hard to teach VBA basics and using the kitchen example is a good idea.
    Some of the things that I would like to automate are:
    – Using Excel sheets or forms to update an Access database
    – Convert a bank BAI file to an Excel sheet

    Thanks and keep up the good work
    Pablo

  • Very well done video. Nice simple graphics. Nice story line. Not too fast and not too slow. Easy to follow. Looking forward to the next videos.

  • Hi Jon,

    Thanks – the video was great. I’m interested in understanding looping processes, working with arrays, using vlookup, collecting and organising data through forms and also in creating/formatting/saving/automating word files from an excel form.

    Thanks

    Angelique

  • That’s an excellent help for developing macros! This is what I need.
    I have a list (worksheet) with info broken down by Date and Name. I’d like to create a macro to draw a line each time the Date changes or the Name changes. They send me the list always sorted by date.
    SITE LOCATION Date Name Type
    Site 2 1880 SCHIEFFEIN 01-Jun-15 George Total
    Site 2 60 PITT STREET 01-Jun-15 George Total
    Site 1 60 EAST 106TH 02-Jun-15 George Total
    Site 1 90 PALADINO 03-Jun-15 George Partial
    Site 1 2201 1ST Street 03-Jun-15 Mary Partial
    Site 1 90 PITT STREET 04-Jun-15 Louis Partial
    Site 1 90 PITT STREET 04-Jun-15 Louis Partial
    Site 1 60 PITT STREET 05-Jun-15 Mary Partial
    Site 1 70 PITT STREET 05-Jun-15 Mary Partial
    Site 1 70 PITT STREET 05-Jun-15 George Partial

    • Thanks AC! In the next video I explain how to list all the worksheets in the workbook using a For Next Loop. You can also use loops to loop through each cell in a range. In your case, you might want to loop through the column and check if the value of the cell is not equal “<>” to the previous cell, then apply some formatting.

      You could also do this without macros by using conditional formatting. You can apply conditional formatting using formulas. In this case you might want to use an IF formula to test if the current and previous/next cells match. I just published an article on the IF function.

      One thing I teach in my upcoming VBA Pro Course is to only use macros when absolutely necessary. Don’t get me wrong, macros are fantastic and there are tons of uses for them. However, there are some limitations to consider and I’ve found it best to use other built-in Excel tools (like conditional formatting) when possible. There are a lot of things to consider, and often times macros and VBA will win when making the decision. It’s just good to weigh out your options at design time. I cover all this in more detail in the course.

      I hope that helps. Thanks again AC!

  • Hi Jon,
    First of all, i have to say that you have a such an excellent way of how to explain and make of it easy to understand. Your videos are simply excellent.

    I would like to learn how to clean data from trial balance, then automate variation analisys, then present financial statements and its charting together with pivot tables, of course all of this by using Macros and VBA.

    • Thanks Rafael! VBA can definitely handle all those processes. When working on automating a process with a lot of steps, I generally try to work on the most time consuming steps first. Breaking it down into pieces can make the project a little easier to accomplish. Especially when you are starting out with VBA. Thanks again!

  • A clear & very detailed explanation of VBA. This is where a lot of people are looking for. I like the comparison with the kitchen table because it gives a lot more sense to understand the difference between objects, properties & methods. The kind of stuff which is overwhelming in the beginning. I’m very curious to the end result of your VBA course. Thanks.

    Cheers
    Jan

  • This is my first experience with VBA and Macros and I tracked with you perfectly. You are an excellent teacher!
    I’m wondering if this knowledge can help with our annual budgeting process. I’ve developed workbooks for each department that have multiple worksheets representing profit and loss centers. Then I’m consolidating each worksheet into one “summary” worksheet totaling the other worksheets to come up with the overall departmental profit and loss budget. There are 8 departments in total and I’m wondering how I might be able to pull all of the consolidated summary worksheets from each department into one organization wide consolidated profit and loss workbook. Is something like that possible using Macros rather than the consolidating data tool?

    Thank you again for the excellent info!

    • Thanks Matt! Yes, this is definitely possible with VBA. In my upcoming VBA Pro Course I have an entire module dedicated to automating a budgeting files. I covers how to create and update the files with VBA. The consolidation part can be tricky. My suggestion is to consolidate the numbers from each department into one large table. Then you can do analysis with pivot tables and create all kinds of different summary reports to view your consolidated data. Of course VBA can help automate all of this.

  • Nice start. I use excel to clean data for uploading to a custom based access application for tracking containers. Import data (Vessel arrival, container info etc) is uploaded to access and when containers are processed for delivery to customers this data is inputted manually. I can have up to 300 containers per week. There is a need to remove duplicates, copy and paste ranges, change cases, & split the original sheet to the necessary data sets.

    • Thanks Steve! Importing data to Access can be done in VBA with ADO connections. This basically allows you to use SQL to add data to Access Tables. It will definitely help speed up your process. Thanks again!

  • Me gustaría hacer un sistema de Clientes, Facturación, inventarios, kardex y contabilidad en excel pero que utilice macros.
    Aunque sea uno de los puntos, sera muy importante para mí.
    Mil gracias.

    • Translation: I would make a system of customers, billing, inventory , accounting kardex and excel but using macros.
      Although it is one of the points it will be very important to me .
      Thousand thanks.

      Thanks Magali!

  • Hi, Jon. Thanks for this tutorial, it is very good explain and it will help me at. Lot with my daily job.

    Can you please do some video on how to use math operations with a useform and text boxes and move all the values to a worksheet.

    Thanks again

    • Thanks Eduardo I’m glad it will help. I explain how to create a userform in the third video of this series, and also have in-depth training on userforms in my upcoming VBA Pro Course. Thanks for the suggestion!

  • Jon,

    Your first video is excellent!

    It succeeds to draw the viewer in to the enticing world of VBA and Macros and I can’t wait for the next video!

    I have to come out with a weekly report every Wednesday. Among other contents, I have to update some 40 graphs/charts. That’s the easy part. The tedious part is to advance 2 data items in each graph one cell to the right, to bring in the current week’s data. I’d like to automate this simple, but tedious and time consuming task.

    Looking forward to your next video.

    Regards.

    Novzar

    Keep it up!

    • Thank you Novzar! I really appreciate that.

      In regards to your charts, you might be able to use dynamic named ranges for the source data of your charts. These named ranges would automatically expand as you get new data.

      A lot of this will depend on the type of chart you are using, and if you want to deal with named ranges. They have their pros and cons. Using VBA to expand the source ranges (instead of named ranges) is definitely a great way to do it as well. I have an article and 3-part video tutorial on how to find the last used cell in a range/worksheet with VBA. This should help with your macro to determine the source data ranges for the chart.

      Thanks again Novzar!

  • Thanks for the video and I am working on a lot of the same things as Janine.
    Format data generated from a database
    Use vLookup to add to data generated from a database
    Join or separate pieces of data (such as names)
    Insert and format a PivotTable Report
    Clean data received from elsewhere
    Looking forward to your next video.

    • Thanks Joe! Those are all very common tasks that VBA can definitely help with. You might also want to checkout Power Query. It’s a free add-in from Microsoft that really helps with the data cleansing and preparing data for reports and pivot tables. Here’s a link to an article I wrote on an intro to Power Query.

      In Excel 2016 we are able to use VBA to program Power Query, which will allow us to get very creative with the reporting systems we build in Excel. Thanks again Joe!

  • Hi Jon,
    Really enjoyed the intro video; could have done with it a month ago as I needed to write code for my first VBA project …. writing a User Form to capture / modify multiple fields which contain restricted dropdown lists.

    The processes that I would like to automate include updating many files with multiple PowerPivot tables and charts (from a SQL database) on a monthly basis. I’ve recently rewritten the tables and charts taking advantage of the new features within Excel 2013 e.g. PowerQuery etc. including auto-updating the files contents upon opening the files. The files (for my board of directors) are in a read only (for them, I can trust them not to rearrange things to their own detriment!) folder. Obviously, I have read / write privileges. It currently takes me nearly 3 hours to manually open and update each file; it would be great if I could somehow automate this process and saves the files (removing the auto-update) so that when the directors open the files, they’re up to date (monthly) and quick to load. Any assistance would be greatly appreciated.

    Thanks,

    Hugh

    • Hi Hugh,
      Thanks for the comment! I’m happy to hear you enjoyed the video. I’m also happy to hear you are using Power Query and Power Pivot. Those tools, along with VBA, will enable you to build some very robust and efficient systems for updating and reporting on data.

      I think you are on the right track with removing the code from your files. Instead, you could have one file that contains the code, and is used to manage the update process on the other files. I actually have a whole module dedicated to this topic in my upcoming VBA Pro Course. We build an application I call the File Manager. It’s basically an Excel file that contains a list of the files you want to update/manipulate. You can then run procedures (macros) on the file list. The code will open each file in the list, then run your code to manipulate the files, then save and close each file. That’s the basics of it.

      It’s basically a framework that you can reuse on any set of files. You can then write individual macros that perform custom processes, and plug them into the application.

      I originally developed the File Manger to create budget files for each department in the organization. Our organization had about 1,000 departments at the time and manually creating these Excel files was an absolute nightmare. The application also handles the process of updating data, refreshing reports, consolidating files, etc.

      I will share a video so you can see an example. I’m guessing your 3 hour process could be automated to a matter of minutes. And you could be getting coffee or knockin out emails while it’s running… 🙂

  • I also really liked the comparison to kitchen equipments. It would be really good to automate the process of ‘Save as’ a number of files after renaming them. All of these files are in ‘ May 15’ folder.

    I.e. Accruals May 15 file Save as Accruals June 15, following month Accruals July 15
    Travel expenses May 15 Save as …. June 15

    I am might asking too much 🙂

    Thank you

    • Hi Anika,
      Thanks for the comment! You are not asking too much at all. In the third video I walk through how to create a userform that exports files. This process basically copies sheets to a new workbook, then uses the SaveAs method to save the files.

      This can be done for your process as well. You will basically loop through all the files in the folder and save each with a new name. In my upcoming VBA Pro Course I have an entire module dedicated to this topic. We build an application that does this, and will allow you to run any macro on a set of files. I call this application the File Manager, and I use it to create department budget files. It can run any kind of procedure (macro) on the files like refreshing data, consolidating reports, and changing file names.

      Thanks again Anika! The third video should help with your process, but let me know if you still have questions after that. It should be available next week.

  • Some of the processes I have (using someone else’s code), or would like to, automate:
    Shade alternating groups of sorted data
    Format data generated from a database
    Use vLookup to add to data generated from a database
    Join or separate pieces of data (such as names)
    Insert and format a PivotTable Report
    Clean data received from elsewhere
    (remove blank lines, unmerge cells, populate a column with group titles, etc.)

    • Hi Janine,
      Thanks for the comment! I’m glad you enjoyed the videos. Those are great topics. In the next video I cover how to add formulas and loop through cells and worksheets. You might want to also checkout Power Query for some of your data cleansing tasks. I wrote an introductory article on Power Query.

      In Excel 2016 we are able to program Power Query with VBA. This combination will allow us to build automated systems for easy data cleansing and reporting. Thanks again!

  • Great introduction to VBA programming. Excellent presentation of Objects, Properties, and Methods, as well as the Object hierarchy.

  • I really liked a comparison to the kitchen and visual explanation of the topic.
    This makes it much easier for me to understand the topic.

  • Thanks for watching! I will be sending you an email with part 2 in just a few days.

    If you received the link to this page from a friend, you can signup here to get notified about the rest of the series.

    Please leave a comment below with any questions. Thanks again and have a great day!

    • Superb video training…. Would like to associate with you if you can give some projects .

      I have been working as an analyst as and good hand’s on excel and Vba. Thank-you

    • Thanks for the analogy, it makes it easier to take in. I am new to the concept of VBA and I am learning more about Excel and functions & formulas to increase my knowledge. I am currently working on a couple of weekly and monthly spreadsheets and hope to speed up this process.

    • Hi Jon,

      That was a very informative video that was easy to understand, thanks.

      I would be interested in using macros to automate repetitive tasks (e.g. formating spread sheets; auditing the contents of regular spread sheets etc), which you will cover, but I am also interested in covering modeling and forecasting.

      Regards

      Paul

    • Excellent tutorial video. Informative, easy to follow and understand. Looking forward to viewing more of your videos.

    • Hello, Jon

      it’s really good explanation.

      i want to know how to select pivot table fields specific multiple items.can you help on this..

      • Hi Nagrenda,
        My friend Jon Peltier has a great article on how to select pivot table fields with VBA.

        Pivot tables are a member of the worksheet. Fortunately, VBA has a ton of properties and methods for the PivotTable object. This allows us to modify pivot tables with macros. My PivotPal Add-in is written in VBA and makes use of all this pivot table code.