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-Reference-Common-Objects.zip

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.

Get the Free Videos

Please click the big green button above to register for the free training series.

Jon Acampora Circle MVP Profile 2019

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,683 comments

1 … 3 4 5 6 7 … 23

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

  • Great introduction to VBA. What I am specifically looking for is to automate reports in case when I have lots of raw data being pasted into an Excel template with formulas etc.

    Reply

    • Thanks Kamil! That is definitely possible with VBA. In the next few weeks I will be sharing more videos and resources to help you learn how to work with data like that.

      Reply

  • Thanks!

    Very helpful intro.

    My question is for the worksheet is it always named as sheet1 or sheet2 or can you reference it per the new name you give it e.g. sales_YTD.

    Thanks again

    Reply

    • Hi James,
      Great question! Yes, you can reference the worksheet by its tab name. In your example, the following line of code would select that sheet.

      Worksheets(“sales_YTD”).Select

      You can also reference a sheet by its code name, which might look like.

      Sheet1.Select

      If you signed up for the free training (big green button on this page above), then I will be sending you a video in a few weeks that explains how to reference common objects in Excel including workbooks, worksheets, and ranges. I think this video will help answer your questions. Thanks!

      Reply

  • Video #1 was very interesting. Easy to follow, understand the different coding you used in VBA and how they integrated with the Excel worksheet.
    Haven’t used a macro in some time, but would like to see how a ‘file save’ can be macro-recorded.
    Thanks, Julia ([email protected])

    Reply

    • Thanks Julia! Using the macro recorder is a great way to learn the SaveAs method. SaveAs is the method you will use to save the file with a new name. There is also an excellent article by Ron de Bruin on how to reference the proper file format. With the new file formats starting in Excel 2007, it is important to reference the correct file format when saving the workbook.

      Please let me know if you have any questions. Thanks again!

      Reply

  • Thanks Jon, this is very informative even for the people that are starting out with excel. I love to find new ways to do things with excel, but I don’t understand everything. You explain very clearly, so that makes it more interesting! Thanks for that. One question. If you have say column “A” with names in it, say first and last together. How do I get the first name into column “C” and the last name to column “D”. In other words, how do I separate the names?

    Reply

    • Hi Steven,
      You can use the Text to Columns feature in Excel. It is a button on the Data tab of the ribbon. Here is the MSN Help article on splitting names with Text to Columns. You will probably want to use the “Space” delimiter to separate column A when there is a space between the first name and last name.

      This can also be done in VBA with the TextToColumns method. It’s probably easiest to record a macro and perform text to columns to get the code for this. There are a lot of parameters for the method.

      I hope that helps. Thanks for the comment!

      Reply

  • Hi Jon, Awesome course, new to macros and vba, but is was good! will start doing some work on macros and vba, maybe nextweek. Thanks again for this free video, I appreciate it!

    Reply

  • Hi Jon, thank you for sharing this Video. Looking forward to more. I’m a macro recorder and a tweaker, which is quite limiting.
    I’m working on a project now which could use a few macros. The report has 3 sections using 3 unconnected data sources. For each one I need to select the Agent and/or region. My thoughts are to create a series of buttons (one per agent and one per region) each with it’s own macro. So possibly 13 or more routines. No idea how else to do it so hope I will gain a little more understanding on how through your videos.
    Number 2 I want a start and end date based on the raw data content the oldest date to the newest.
    Number 3 and the most complicated (I’ve been pondering for 3 days) is: I am restricted on the output “it is what it is” My data is in columns (14 x REASONS) but I would like to get them in one column so I can workout % of all. Each row may contain 1,2,3,4 or 5 columns of data but I can only work on one per row. At the moment I work a bit like your friend Andy. Copying the common detail (1st 6 columns) then cutting and pasting REASON columns and paste them one at a time below each other. I start out with 1260 rows and end up with 17640 (can vary). This is done on a weekly basis so like with Andy is very repetitive but as a novice it’s the best I can do.
    So Jon, I am very much looking forward to your knowledge sharing and hope I can save myself time and ensure data integrity. Thank you Olwyn

    Reply

    • Hi Olwyn,
      Thanks for sharing some info about your projects. It sounds like you could definitely use some macros to automate those processes. I’m not completely clear on your 1st project, but you can probably do it all with one routine. In VBA there are ways to determine which button was pressed using the Application.Caller property. That is probably more of an advanced technique, but it can help eliminate a lot of repetitive code or macros.

      I think this series will help you see what is possible with VBA to start automating your projects. Please let me know if you have questions as you go through the videos. Thanks!

      Reply

  • Love the introduction to VBA. I have been searching and you have a great presentation. Looking forward to the other videos.

    I am looking to automate the updating of my dashboard. I have 9 different tabs of various data and I would like to have it updated with VBA

    Tony

    Reply

    • Thanks Tony! Updating dashboards is definitely a job for VBA. VBA can work with just about every object in Excel including charts, pivot tables, shapes, formulas, and any other components of your dashboards.

      Reply

  • I’m interested in writing macros that doesn’t hard code ranges. For instance this month I have 2 columns and 5 rows. That would be 10 cells that are included in my range. The next month I have 4 columns and 10 rows. That would be 40 cells that are included in my range. I want my macros to be flexible and not be dependent on the number of columns and rows when the macro is run each time.

    I also want to learn how to write efficient macros so that they run as fast possible.

    Reply

    • Hi Rick,
      Great question. In video #2 we talk about variables. Variables are a very important topic in VBA (and coding in general) because they allow you to specify references based on conditions. This means you don’t have to hardcode range references.

      The following lines of code will all select range A1:C5, 15 cells.

      Range(“A1:C5”).Select

      Range(Cells(1,1), Cells(5,3)).Select

      Here is the same code using variables to eliminate hardcoding.
      lRowStart = 1
      lColStart = 1
      lRowEnd = 5
      lColEnd = 3

      Range(Cells(lRowStart, lColStart), Cells(lRowEnd, lColEnd)).Select

      I will be sending another video in the coming weeks that talks more about common references including worksheets and ranges. Hopefully that will give you a start though. Please let me know if you have any questions. Thanks!

      Reply

  • I have multi-line reports and I would like to automatically do data sorts where certain data is then copied to a new worksheet with a specified new name and where automatic totals are created. Certain columns on that new sheet would be chosen and a total for those columns would also be calculated. This is all basic macro stuff that I should learn and become comfortable with.

    I’d also like to be able to automatically paste a selected column of new data onto the ending open column on a specified existing tab that has multiple existing (previously copied) columns of contiguous data. I’d also like this data to be formatted in a certain way.

    Reply

    • Thanks Rob! Those processes can definitely be automated with VBA, and I think it will save you a lot of time. Please let me know if you have questions as you watch the other videos in the series.

      Reply

    • Hi Sherrylynn,
      I went ahead and registered you for the free training videos.

      For anyone else with that question, you should be able to click the green button on this page below the video. You will be prompted to enter your email address to register for the free training. Thanks!

      Reply

  • Hi Jon,
    I have almost missed out this great introduction to Macro and VBA if it wasn’t a reminder from John Michaloudis! It very straightforward great! I have been using Macro Recorder for simple repetitive tasks. I would like to learn more about the potentials of use VBA. Look forward to the next videos.
    Thank you,
    Susan

    Reply

  • Video 1 was an excellent refresher. I have been working in the VBA editor editing existing macros but now need to learn to write my own from scratch. Looking forward to the next videos.
    Cheers!

    Reply

  • Hello Jon
    Please i am interested in attending the free training for 8pm today. You message said i should click a lick below but there is nothing to click. kindly sent me an alternative to registering for training as mentioned.

    Thank you

    Sarah

    Reply

    • Hi Sarah,
      The free training is a video series that you can watch any time. It is not a live meeting. You can click the green button the page above to register for the free training and I will email you the other videos in the series. Sorry for the confusion. Thanks!

      Reply

  • When will video #2 and #3 be available? I have had other people write macros for these tasks in the last 2 months but I want to learn and I’m wondering if we did them most efficiently.

    Reply

  • Hi Jon, thanks for sharing, I need to automate data entry such as temperature, pressure, flow, etcetera from external sources (industrial process instruments).

    Reply

  • Video 1 is really nice because it touches the fundamentals and teaches basics of Objects and Properties.
    Thanks

    Reply

  • Jon,
    This was a great introduction. Being someone that is self-taught there is a lot of specific terminology that I did know or understand. This video really helped clarify some of these items for me. Thanks!

    Reply

  • Hi Jon –

    Such a clear video!!! Thank you! Also loved your add-in to move the labels on charts; it was exactly what I needed 🙂

    I’ve been running a number of repetitive reports. Each report has a variable number of rows and columns.

    I want to select the whole range, run conditional sort based on the 3 cell colors in the header row. Here’s what I’ve been doing:

    Home – Sort & Filter – Custom Sort – Options (Sort Left-to-Right) – Sort by Row1 (Cell Colors) in A-to-Z order (3 rows to represent each cell color).

    Do I understand correctly that to watch the videos 2 and 3, you will send separate links?

    Reply

    • Thanks Nadine! Yes, I will send separate emails for the next two videos.

      Your filtering process can definitely be automated with VBA. You can program the AutoFilters in VBA to filter by color. You might want to try and record a macro while you do this process just to get the code and see what it looks like. The macro recorder won’t give you the most flexible or efficient code, but it is a good way to learn the code you will need to automate the process.

      Thanks again and have a great day! 🙂

      Reply

  • Can you help me to teach how we can write VBA code to import the file into excel and then running to set up template we created with formula inside. If you want to more details , i can sent the example of excel sheet that is already set up VBA code but i could not understand each code.

    Reply

  • Hi Mr Jon
    The processes which I wish to automate relates to generation of reports from raw data including hyperlinks, dashboards….

    Thanks in advance for any kind of help.

    Reply

    • Hi David, You should have received the email for the second video today. Please let me know if you did not receive it. Thanks!

      Reply

  • Hi as i have read I cant share the Files of PivotPal?

    I have to prepare reports for my Manager.

    Using PivotPal:- can I forward the dashboard/ reports to my manager &
    also use it in my PPT presentation? If Yes, How ?

    Also how to get the print outs?

    Reply

    • Hi Salim,

      Yes you can send the files to your manager. PivotPal does not add any additional code to the file. It is simply a tool that makes it easier to work with your pivot tables by automating processes. Your manager will still be able to modify the pivot table without needing PivotPal. Please let me know if you have any other questions. Thank you!

      Reply

  • Thanks Jon
    Video was perfect and hope my subscription excel campus
    will bring to me more advance progress in macro

    Thanks

    Reply

  • I really like your teaching style. I’m not a total beginner at VBA but since I am self taught I know there are many little beginner things I’m not aware of. It seems when I am working with excel the things I want to do are unusual. My latest little conundrum is to use the name of a table either as a heading above the table or as information in a cell within the table. This seems to be unheard of. I was hoping to just find a function but it looks like I will have to code it instead.

    Reply

  • I would like to import a csv file and parse it using VBA before I manipulate the worksheet. Can you show us the steps?

    Thanks!

    Reply

  • Hi! Thanks so much for the videos. I am relatively new to VBA and learning fast, but I get hung up on how to automate a macro to process files in the same way that have different numbers of worksheets. I know it has to do with if/then loops, but can’t seem to make it work. A tutorial on this would be great!

    Thanks again!

    Reply

    • Thanks Jess! In video #2 we talk about this exact process of looping through all the files in the workbook. Please let me know if you have any questions after watching that video.

      Thanks again and have a great day! 🙂

      Reply

  • Good refresher for Macros! I like it! And I’ll show others just what their missing with macros.
    I know almost everything can have a macro written for it but sometimes, I think people just go a little overboard with them. But I like this course on macros. NICE!

    Reply

  • I am a MS Access guy, and never really got to more than an intermediate level in Excel. But employers seem to want Excel skills beyond what I currently have. I have already watched your pivot table videos on YouTube. Great stuff! At this point, I am reasonably competent at those. Now I’m watching your macro videos.

    The process I’d like to automate is this:

    I download my credit card transactions each month into a CSV file. I then convert it into an Excel spreadsheet to (later) import the data into an Access database. However, before I complete the import, I do a few things to the Excel file. First, I replace the column headings and add several more headings (for tax purposes). Second, I add the year and month (YRMO) to each row so I can link each transaction to the specific credit card bill. Third, I add the transaction type (CC) to each row. And fourth, I add the tax year (YY) to each row.

    I know I can automate much of this with Access. I just haven’t gotten around to it! But this seemed like a perfect opportunity to learn more about Excel Macros. Is this all possible in a macro?

    Reply

    • Hi Bill,
      Yes, it is definitely all possible with a macro. Just about every manual action you take in Excel can be done with a macro. Just one of the things that makes Excel awesome! 🙂

      Reply

  • Thanks for the video. Great information.
    I am needing to pulling a variety of data from various websites. I have the macros to massage the data, but now I need to be able to copy a range of let’s say A1:B1, but only cells with values. I need them to go to workbook 2, sheet1, range of column E where it finds the first empty cell and paste the data.

    Reply

  • Hi Jon,

    It was a very good and clear video about VBA for starter like me. I have 1 question if you on the VBA. I want to automate small part but repetitive task of my work. (creating a bi-weekly invoice file).
    1) How can I copy value from sheet1 skipping every 4 column. (Example – copy Values from column A, F, K ….) and paste it in sheet2 continuously (in column A, B and C…).

    Thanks
    Akash

    Reply

  • Hi – I work for an elect. const. co. I am trying to automate inventory sheets for our trucks. When some product is used, it is marked on the sheet for me to put into our master pricing sheet, – I have it sorting by Qty of items used, now I want to make a macro that copies that info to a pricing breakdown sheet, totals it, adds tax and mark-up, prints it, and resets the inventory sheet back to a blank to use again. I get stuck when I try to write a code for “Copy, end – right, end – down then paste values to another sheet. then to end down, autosum, and so I guess I need clarification on what the proper terms are when I am writing code. I used the “Record Macro” function, but if the copied lines are more than they were when I recorded it, it puts the total in the same line it was when I originally recorded the macro (I.E. G31) no matter if the next time I copy the info over, it goes down to G53, the Total will still total at G31. THANKS!

    Reply

  • Long time user of Excel, since the first version, however my first look at VBA. This is training that is very sharp, concise and to the point, delivered in a way that is very easy to follow and understand.

    Thank You Very Much…..

    Reply

  • This video was excellent, I haven’t found any other tutorial that breaks down the way Excel/VBA thinks and organizes it’s elements[object, properties, methods]. I look forward to watching even more!

    Reply

  • Hello! I got a request from a client to extract specific data from over 120 workbooks and compile it all into one workbook. I think I can do it using a macro. I have NEVER created a macro before so I am excited to use your tutorials to try and create a macro that will get me what I need. Thank you for the tutorials, they are great! Wish me luck!

    Reply

    • Thank you Soraya! That is a very common task for VBA. I actually have an entire section dedicated to that task in module 11 of my VBA Pro Course. It’s great for pulling data from files, or updating all the files with data. Thanks again! I’m happy to hear you are enjoying the videos.

      Reply

        • Hi Alex,
          The course will be open for enrollment in just a few weeks. Let me know if you have any questions in the mean time. Thanks!

          Reply

  • I’m a longtime user of Excel – and Macro prior to VBA (even back to LOTUS 1-2-3). I’ve learned mostly from trial and error. I thought your explanation was FANTASTIC. It has filled in the blanks of things I knew, but didn’t truly understand. THANK YOU!

    Reply

    • Thank you Brigitte! I am so excited to hear that you learned from the video. Please let me know if you have any questions as you go through the series. Have a great day! 🙂

      Reply

  • Debra Dalgleish pointing my in the direction of your videos.

    Loved the first video, very well presented, easy to understand and follow. I have been using Excel for many years and always shy away from getting involved in VBA Macros, instead I try to find a solution through conventional use of Excel. I am currently working on specifying the requirements for commercial software for a third party to develop. I am finding that it is better to create models of what I need using excel and others can then convert this into web application code.

    I am now going to view the other training modules and then have a go at writing some basic VBA myself.

    Reply

    • Thank you Ian! It’s great to have you here. I have used Excel to create wire frames and mock-ups of applications as well. It’s a great tool for that. I sometimes find that it is best to just have the application in Excel using VBA, but a lot of that depends on the scope of the project. Thanks again and have a great day! 🙂

      Reply