Automation with Excel Macros - Getting Started - Absolute Beginners | Steve Tran | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Automation with Excel Macros - Getting Started - Absolute Beginners

teacher avatar Steve Tran

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

25 Lessons (1h 27m)
    • 1. Introduction

      2:34
    • 2. Showing Developer Tab

      0:34
    • 3. Recording Macros

      2:41
    • 4. Saving XLSM file type

      1:10
    • 5. Viewing Your Code

      1:27
    • 6. VBA Editor Message Box

      2:47
    • 7. Cell Referencing

      1:50
    • 8. Naming a Cell

      2:25
    • 9. Create a Folder

      2:48
    • 10. Error Debugging

      3:31
    • 11. Writing Data to Cell

      1:02
    • 12. Variables

      1:13
    • 13. Basic Math Integers

      1:36
    • 14. If Statements

      3:14
    • 15. For Loop

      4:18
    • 16. For Loops Continued Automation!

      2:51
    • 17. Class Project 01 Description and Tutorial

      14:37
    • 18. Moving Data - Create Form Record Macro

      4:24
    • 19. Moving Data - Name the Ranges

      7:11
    • 20. Moving Data - Simplify Recorded Code

      5:12
    • 21. Moving Data - Paste Values Only

      4:19
    • 22. Moving Data - Without Copy and Paste

      3:35
    • 23. Moving Data - Writing to Next Row

      6:00
    • 24. Moving Data - Add Timestamp

      1:35
    • 25. Moving Data - Writing to Different Sheet

      3:59
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

104

Students

--

Projects

About This Class

This class will introduce you to the concepts of programming and automation using Microsoft Excel with its' macro and VBA coding functionality.  It is for complete beginners that may have never written code before in any language.

It will walk you through complete basic concepts and slowly demonstrate some of the powerful automation that this skill can bring to your clerical computer based tasks.  

Once you're done, check out my other class where we implement some of these concepts and others into an actual tool that can help you manage your projects and tasks that you're working on.  Macros - Automated Project List

Please feel free to contact me with any questions and leave a review.

Meet Your Teacher

Teacher Profile Image

Steve Tran

Teacher

Hello, I'm Steve.  

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hi everyone. This video series is a class on writing Excel macros. To be honest, these are the first video tutorials I've ever made. I watch video tutorials all the time on all sorts of topics and I want to contribute to the community. I chose Excel macros because it's a topic I feel I know very well and something that's useful to most people, whether for their own projects, their own accounting, or the business that they're working in. Other making macros from simple to very advanced for around ten years now, I feel like this is a very common story, but my job when I first started writing macros had nothing to do with programming or automation. I then realized I can make my job easier for myself and my coworkers by automating some of the routine and repetitive tasks we are responsible for. This class is for absolute beginners who have never written a single macro or line of code in their life. In this course, I'll introduce you to the very foundational concepts of writing Excel macros, the programming language VBA, which macros are written in, and the concepts of computer programming that even apply to other programming languages. You may find that some people out there ask why write Excel macros instead? Why don't you use these newer, more modern programming languages that people use to write web apps, phone apps, et cetera. I'd argue that in my experience, Excel is one of the most universal tools utilized nearly every business in the world, from small businesses to the largest companies in existence. Macros are also more powerful than most people realize. They have the ability to not only control in excel file, which they can in basically every way imaginable, but also to interact with basically every aspect of your PC. Once you get to an advanced level. It's also something that requires nearly no setup for both developers and users. As long as you have Excel installed, you're good to go. All these make it an amazingly effective tool to learn for nearly everyone in this class. I've edited the videos to cut out most of the gaps and unnecessary pauses. So those of you that want to fast pace can just fly through the content without getting bored. The nice part about this being a video is that those of you that want to slow things down a bit can pause or rewind. The project at the end will be an assignment to take a list of numbers and parse it into another list based on whether those numbers are less than ten. This may seem like a very simple task, which it is if you did it manually, but it'll create the building blocks for you to create highly automated tools and future. Hope you enjoy it and thanks for taking the time to participate in this class. Leave any comments along the way and I'll happy to respond and also take ideas for other classes in the future. 2. Showing Developer Tab: As you start to fall along with this tutorial and pull up Excel on your own computer. You might notice that you don't have a Developer tab here, like I'm showing on my screen. That's because by default the developer tab is hidden. In order to unhide the Developer tab. Go to file, down here to options in the bottom-left, Customize Ribbon. And then over here on the right, you want to check the Developer tab if it's unchecked, and then click OK. And then you should now see your developer tab, which will show you all these tools that you're going to use to create your macros. 3. Recording Macros: I wanna show you guys how to use the Record Macro function. And this is built into excel and it's something I used a lot when I first started working with macros. So if you come up here to the top left, you'll see this record macro button. You can click that. And then this forum pops up and it asks you the provide a name for the macro you're recording. We're going to call it example macro and click OK. And you see that this button on the top left change to stop recording, which implies that you're recording right now. And that's exactly what's going on. Whatever I do in my spreadsheet here is being recorded, including clicking these random cells. You'll see that that's actually being recorded and you'd be played back later. I'm going to create something really simple here. So let's say I just do something like highlight these cells and I want to write a high message. And then what I just did here was all recorded. So if I go back to my Developer tab, it still has the Stop Recording button, which means that we're recording still, I can click stop. So if I come over here, I highlight these cells. I'm going to undo what I did here because we're going to play the recording back. If I go to my Developer tab, I click macros. That shows me the list of macros I have available. And so example macro being the one we record it, I'm going to click run. And there you go. You see that it just played back the actions that we recorded. There's a really simple example of just how you can take a list of actions and play them back and perform them in a click of a button. And in this case not actually a button, but I can show you how to do that too. So I can come up here to Insert list. Here I have a bunch of components for forms. And the one I'm going to use here is the button control. And I'll click that. And I can just drag and drop a button here. If asking me which macro I wanna run with this button, I'm creating a quick example macro. Click OK. I can change the text here. And then if I clear this again, click, say hi. You can see it runs the macro. And I'll just do this again. We're going to record another macro that clears this out so I don't have to keep manually doing that will just leave this as macro for we're recording. I stopped recording. I go to insert, create another button, drag it, have it run macro for. And there, yeah, now you have two buttons. One that says hi and a second one that clears IT. 4. Saving XLSM file type: I made this basic macro here where you click the say hi button and it shades in all these cells, so it gives you a high message. And then when you click Clear greeting, it'll just clear that out for you. So you can use this back and forth. And then now I have my file and I want to save this file. If I go up here to the top left and click the save button, I see this message here. And it basically tells me that I'm saving this file as an XLS file type. And so that's the default for an Excel file. You can see that up here into header. It's basically telling me that this is a macro free workbook file type, which means that if you save it like this, the macro that I created isn't going to be saved If I click Yes here, next time I open up this file, the macro that I created is going to be gone. So I'm not gonna do that. I'm going to come over here. I'm gonna click No. So it's going to bring me here to the save as menu. So if I come over here to this drop down, I can click this and select an excel macro Enabled Workbook or an XLS file type. I'll click save. I don't get that error message. And this is a file type that when I bring this file back up, or macros will still be in here. 5. Viewing Your Code: In the last video, we went over using the Record Macro function up here on the top left, you might be wondering where the code go that we've recorded. How can I make an edit to that if I wanted to. And so that way we can view that macro is, and go up here to macros. You can see example macro is what we recorded. If you click edit, that brings you to the code editor so you can see what we recorded here. I clicked into the K4 cell. I changed the background color of that cell to black. I started copying and pasting that cell till I made this high message over here. So another way I could have gone to the code is just coming over here to Visual Basic that also opens this code editor. And then I can navigate through here once I get here. And then the third way I typically get to my macro code is if you highlight a button and a trick here is if I click this button, I'm not going to just highlight the button. I'm going to run the macro. And since I don't want to run the macro, I just want to highlight the button. I'll right-click this and then click out of this context menu. And then now the buttons highlighted without running the macro. Once this button's highlighted, I can come over here to view code. And that's going to take me directly to the code that the button was going to run. And here you are at example macro. And I'll show you again if I go to clear greeting, Go to View Code. That button was going to run macro for. And so there's a couple of ways where you can get directly to the code of your macro. 6. VBA Editor Message Box: Now that we are looking directly at the code editor for our Macros, I can show you that the structure for each macro is written into these subroutines. So if I use an apostrophe character, I can leave a comment here. Macro code goes here. It highlights green showing you that it's a comment and it's not code that will actually run when you play this code that's written in here is in the language called Visual Basic for applications. And you can see up here this Windows called Microsoft Visual Basic for applications, typically people refer to it as VBA for short, VBA. So if there's something that you don't know how to do, the easiest way to do it is to just go to Google. And I do this all the time, even for things that I've done a 100 times. And you start by typing and VBA, and then whatever is you want to do. And so if I type in VBA message box, you can always try the first link. And you'll see you can easily find the code notation for whatever you wanna do. Some things take a little bit longer to find the answer to. But for simple tasks like this, I Google solutions all the time just because I don't memorize the notation of every single command in here. So here we want to look up how to make a message box. You see this notation is right here. We're just going to trust it will go over back here, type this. And if I wanted to run this macro, I can do so by coming up here to this Play button and clicking it. You go, you have the message box. I'll click OK. I'll highlight this. I'll make another line. Change this two. Here's another message box. And another thing you can do is instead of coming up here and pressing play, I can run this one line at a time and I can do that with the FAA button. And so if I press F8, you see this yellow arrow pop-up that tells me to line that's about to run. As I run that this first message box will pop up. I'll click OK here, it takes me back and then I can run the next message box. And then next step, the macro be complete. Something else I wanted to go over here is you might notice that the example macro is written in this notation where it says sub example macro, open and close parentheses, and then n sub. And that's the notation here because every macro is essentially a subroutine. And this editor will automatically handle that for you in some ways. So if I wanted to create another macro and I type sub example macro to, if I just hit Enter. You can see it automatically created the notation for you to be able to code and other macro right in here. 7. Cell Referencing: As we create macros, we're going to want it to interact with our spreadsheet. So what you might have is a basic form. Let's say it looks something like this. We're going to want our macro code to be able to interact with this spreadsheet. And so if I come over here, I have this message box. I can change what that message box says. If I run it, it's going to say whatever I have written in here, I click OK. I'm gonna wanna put my name Steve over here in the message box. So the question is, how do I do that? I can use this ampersand sign to combine two strings together. And so I have the text, My name is on the left, and I want to get the value of this cell over here, and this cell is in between. Then I can do that by using the range function. And the range object is basically a cell or group of cells. And here in the parentheses is the name of the range. And you're gonna see here, after I type the range, I can hit period. And then this shows me this menu of all the different properties that the range object has. And there's a ton of them in here. And the one I want to use is value and you see it jumps down to that. And so that shows me that value is a valid property that exists. If I tab out of that here, we've updated our message box. So it'll take the value of the cell B2. If I run this. There you go. It says my name is Steve. One other thing I should show you is when I click this Play button, it's going to run the macro that my cursor is highlighted in. So if I click out of this subroutine and then click Play, you can see it asks me which macro I wanna run. Right now we only have one, but if I had a list of a 100 macros, it can't figure out which one we wanted to run because our cursor was not sitting in a specific one. 8. Naming a Cell: Here over on the right, we have this basic macro that we created. It's just going to display a message box that says my name is. And then it references the cell named B ten. And that's over here. And click play. And there's the message box. So one thing I want to show you guys, and this is something that I wish I knew way back when I first started making macros because it would make my code much better and much easier to update, is the ability to give ourselves a specific name other than its default btn. And the problem with leaving the cell as referencing BY ten is that as you keep developing this file, this cell can move all over the place. And so for example, if I added some cells here and add a new row, this is now BY 13, but my code over here still says B ten. And so if I run this, it's not going to work anymore. And so how do I solve that problem? How can solve that is by giving this cell a name. The quickest way for me to do that is to select a cell. It's called be 13. Right now, I can come up here in the top left and just change it. I'm changing this cell to be called name. I hit Enter, it just registered. And if I click in the cell now you see instead of saying be 13, it says name. These other cells that don't have names, they still say their original address. But now if I go over here to my code and I changed my range name to be called name. I know I'm making this as confusing as possible right now. I can run this code. It's going to know the cell reference. But then if this cell moves around and I grabbed some rows, I delete them, I'm going to run this. And there you go, it still works. And that's because if I come back to this cell, when I deleted those rows, it knew that this cell is called name and it moves around with its reference. And I can see all these named cells. If I go over here at the formulas, I'll make this bigger for you and go to a name manager. I can see all the ranges that I've named in this spreadsheet. And so here you can see, again, as confusing as possible, The name of this cell is name. And it says right here that it refers to Sheet one, cell B21. And right now we only have one. But if you have many different cells that were given names, you'd see them all listed here. 9. Create a Folder: I'm going to show you guys how to use a macro to create a folder. And I think this is a great example of how you may think of macros as something that automates your spreadsheet. But by using a macro, you can do things that you wouldn't otherwise do within Excel. Something like going through your file explorer and creating a folder. So I'm gonna come over here to my code editor on the right, I'm going to start a new subroutine. And I can type my macro name here. If you hit Enter, it'll automatically create an end to your subroutine. I can name this create folder. And now I want to add a line of code to be able to create a folder. And the create folder function is MKDIR. And you can see here as I open the parentheses, it's showing me first that it recognizes this function, which is a good thing. So that means that actually exists. And it's telling me that what it accepts within its parameters, within the parentheses after the function is a string and that string is the path. And so that's basically the address of the folder that we want to create. So the first thing I wanna do is just test out this function. And I already created a folder here. It's in the C Drive. It's called C Drive example, and that's where we want to create art. As I click back into the code editor here, it's just popping up this error telling me that this is not a valid line of code. Of course it isn't. It's because we left the window with the line of code half written. I'll click OK. I'll come back to it. It's a string, so we're gonna put it in quotations. We're gonna write C Drive example. That's where we want our folder and our new folder name we're going to call test, will close that quotation, close the parentheses. And we'll try this out. Hit play. And there you go, you see a new folder that was created called test. Next we're going to combine a concept from our previous video where we can create a folder based on the content that's in our spreadsheet. So over here back in our spreadsheet, I had already written this. I have this cell. It's called folder names. In here. It says Folder one. I can name this cell folder one. If I come over here to this string, I can use an ampersand to combine a string together. And I'm going to want to add range folder one dot value. And so now when I create a folder path, the path is going to be this all combined together. So C Drive example, and then the value of range folder one, which right now just says folder. And so if I go over here, I click into this macro, I run the code. I come back to my example folder and they go, we have another folder called Folder one. You see there is a space here which is the content of this cell. 10. Error Debugging: In our other video, we threw together this quick macro where if I run this over on the right, it's going to create a folder based on whatever I have written in this cell over here, which is called Folder one. I want to show you guys what happens if there's an error in the macro. And what I know is going to happen here. If I run this again, there's going to be an error because it's going to try to create a folder in the path C Drive example folder one end because we ran this macro already. I know that there's already a folder there with that name. And so we're going to have a problem where it's not going to want to make another folder with the same name because it can't overwrite it. And so I'm going to run play. And you see this error message popped up. And it's telling you the error we have is path file access error, runtime error 75. I happen to know what this means and we already knew this was gonna happen. And the axis error is because it's unable to create the folder due to one already existing. Now if you guys didn't know what this meant, if it was a different era and you weren't sure what was going on, you could always take this runtime error code. I'll just grab this, I'll type it into Google and I'll put the phrase VBA until the beginning of the statement. And you'll usually find some sort of discussion or results about what the cause of this error might be. And then next, if you come back to the error itself, you have two buttons here really that you're going to use. If you hit end, it's just going to stop when it's trying to do. But if you hit Debug, it's going to bring you over to the line of code that caused the error. And this one's nice and easy because there's only one line of code. And so we obviously knew that this was the cause of the error. But if you had a macro with hundreds of lines of code, this would take you directly to the line that caused the error and highlighted yellow. Another good thing to know here, and I'll make this bigger is if you come to this screen, you'll see that this says break, and that means this is in Run mode when you have a yellow arrow here, that means that this is live and it's trying to run a macro or it's in the process of trying to run it. If you click play is going to continue down your macro from where the yellow arrow is. So if I click Play, this is going to continue to run. It still has an error here and I'll hit end. And so you see that break went away, and that means it's no longer trying to run a macro right now. So I can better illustrate that. I'm going to put debug print message here, one. And then on the other side of the statement, I'm going to grab this unquote message here too. And if I click run, we get the error again. I hit debug. And you can see we're on this line here where the error is. When I type debug print in a message. You can see it shows up down here when it runs that line of code in what's called your immediate window. And so if I hit stop and then run this again, it's gonna do the exact same thing again, is going to run this first line of code, gets stuck on the second line of code and there's your message. If I fix this problem, however, I can come over here. I'm going to take this folder, I'm gonna delete it. Now. If I continue running, I'm going to continue running from this line. We're not going to see message one here. The folder is going to successfully create and then we're just gonna see message two. And there you go. And these messages just the pen to the bottom. So you saw a message to show up here. We didn't get the error because there was no longer conflict and existing folders. And there's folder one again. 11. Writing Data to Cell: Alright, so in another video, I showed you guys how to take text from your spreadsheet and use it in your macro. The opposite side of that, that I want to show you guys is how to use your macro and then write something back into your spreadsheet. So I can do that. If I take a range and I can take anything, I'll call it range be ten. And say dot value equals put text in btn. And I'll put that in quotation marks. And I run this. There you go. It's as simple as that to write taxed with your macro into your spreadsheet and combining some concepts together again, you can grab a cell. I showed this in another video. You can call it, let's call this phone. And then if I come over here, I can actually change range to phone. And if I run this, there you go. And place the value into your phone reference cell. 12. Variables: What I've showed you guys before is how to reference information from the spreadsheet. So right here I have this example macro. When I click it, it shows me a message box and it takes the value of cell B1 from in here. Next thing I want to show you our variables that you can use directly in your VBA code. Because sometimes you don't want to pull information from your spreadsheet. There's information that you want to handle only in your code. So I'll come over here, creates a new lines. We'll clean that up. Denotation to create a variable is dim, variable one. As we're going to make this, a string type is string is basically text. And so now that I've declared this variable, I can now use it. And I can type variable one equals different text here. And it's now anywhere I use this variable, one will be referencing the string that we declared it over here. So I'll come over here to our message box. I can change this reference that is right now referencing directly to our spreadsheet, the cell B1. I'm going to change that to variable one. And if I run this, the other message box pops up with the value of our variable. 13. Basic Math Integers: And our other video we showed you that you can create a variable. I first declaring it with this DEM variable as string. And then you can assign a value to that variable, and then you can use that variable. In this case, we put it in a text box. And so if we run this variable, one is different text here. Another variable type other than a string that you'll use very often are numbers. Very often you'll be using integers, which are numbers without decimal points. So we can change this to an integer. And since we've declared variable one is an integer here where we assign it some text that's not going to work. And so if we run this, you see we have an error. It's a type mismatch. And that's because we are assigning a string, a variable that is an integer type. And hit debug, it shows you the line that went wrong. We're just going to stop running this here. I'm gonna change variable one value to one. I'm going to create a new integer. And that's called result. And we can do some extremely basic math here. Result equals Variable one plus variable one. The result is the result. We'll run this. And there we go. We created a variable, assign it a number, did some math. And here's the result. 14. If Statements: Now we're gonna go over if statements. This is where we're actually adding some intelligence to our code, where the code is able to make a decision for us. What I wanna do is I want to have our code reviewed the number that's in this cell here, and then tell me if that number is greater than ten or not. And this is something that you can do with a formula. But I just wanted to show this as an example that you can do also through a macro. And with the macro you can build this up to become much more complicated than you can typically with a formula. So to ceiling is way higher. So I'm going to come to my macro. I'm gonna create a variable to hold our input value over here. I'm going to write dim input value as integer. Input value equals range B7 dot value. And now for our if statement, we're gonna type if and then notation for an if statement is if parentheses condition goes here. I'll come back and fix that. Then it doesn't like that because of course this isn't a real condition. And if, and so if we create our condition here, input value is greater than ten. Now we've created a conditional statement where we're only going to run the code that's in between the if and the end if, when this condition is met. And if we meet that condition, will give the result in cell B8 equals yes. And I'll step through this code. I'm going to use the FH shortcut. Where you go, we'll run this first line. We declare the input variable. We check the if statement, and we did not meet the condition here. And so we skipped over this line of code. Now, let's try a different number in here. We'll say 11. And if I run this again, and right there you can see since this condition is met, now we're in this statement and we're going to run this line of code. But we're going to have a problem here. And so if I come back here, but the number three and I run this again, they actually ran a random split seconds, so you might not be able to tell. But when we fall along, what we know happened is that the value three did not meet the statement. And so all it did is leave this value in here. We didn't add another yes, but you can't tell. So I'm just gonna put a message box here so you can see that it actually ran and nothing happened. And so we don't want yes. To stay in here. That's from the last time we ran this when the input was 11. What we're gonna do is we're gonna create an if else statement. With this else statement, the code that goes in here is going to be what happens if you don't meet the conditions of the if statement up here. And so if we don't meet that, we're gonna do the same thing. Equals no. And we'll run this again, complete. And you can see now when we don't meet the greater than ten condition, it runs this line of code, the else statement. And so if we go back to 11, run this again, it'll switch back and forth. 15. For Loop: In this video, I want to show you guys for loops. And these were the automation really happens where you can accomplish a ton of actions automatically. And so first, I want to show you an extremely basic four loop. We don't even need any content that's over on our spreadsheet on the left. The for-loop notation is four. And then you need a variable which is essentially your counter. Typical notation is to just use the letter i equals 123, let's say. And then next I. And so I'm going to create a message box here that displays the value of i. And when we run this, the first time we go through this code I is going to start at one here. This is going to display a one. And then when you get to this bottom of the for statement, is going to increment the variable i by one. And then so it will come back to the top of the loop and it will run with i equals two. And it'll do that all the way until i equals three. And then it'll be the last time that this runs. And it will know to leave the loop after you reach this criteria over here. And so let me run this. Here you have your message box of i equals 123. And then we're done. Now I want to show you how you might be able to use something like this to interact with your spreadsheet. So here I have three messages. So I'm gonna create a heading for this list here, bolded control be control u. And here I have three messages, and I'm going to call this message list. Right now this cell here is named the message list. So I'm going to use that as a reference point. For now. I'm going to comment this out. And I can comment this out by putting these apostrophes over here. And that turns these green. It makes them comments so they're not going to run. If I press play again, there's basically no codes and nothing happened. Something I haven't showed you yet is that if I reference this cell message list, which again is sell eight over here, I can use this function that's part of a range called offset. And you see it gives you a row offset or column offset. And so from the cell that you're referencing message list here, I can give it a number which is my row offset, which tells it that you want to offset this cell by one, which will bring me down here to message one. Message popped up again and again because I left this line when it wasn't a valid line of code. I'll come back to it here. I don't want to move my column at all because we're still in the same column. If I typed one, we will be moving one cell over to the right. We'll leave that as 0 value. And if I'll wrap this in a message box again, so we get the CD output. I'll run this. And because we're at message list, we go down one row offset. Here we see message1. Now comment this out and we can use the same idea in a loop. I'll come down here. And the other thing I'll show you really quick as I'm typing in these apostrophes. A faster way is if I go to view toolbar and edit, this gives me this floating toolbar over here. I can drag this up top and this button right here, we'll comment or the one to the right of it. We'll uncomment a whole block of code. But so how can I use this offset that we just demoed in our loop? So in our message box, we know the value of i is going to run three times with i equals 12. And then three, we're going to add a cell reference just like above here, message list. And instead of putting one here like we did above, we're going to use the variable i that changes every time the loop runs. So I change this and make this an I. And so each time this loop runs through, the value in this statement is going to change. And so I'll run this here. First loop message 1, second loop message 2, third loop message three. And so as we take this idea and apply it to more complicated macros, you can start to see how these loops can be used to perform repetitive tasks. 16. For Loops Continued Automation!: In another one of our videos, I showed you how you could use the macro to create a folder. I want to combine these two to show you how useful the for loop can be. And so I just created this example where if I run it, it's going to loop through the messages in each one of these three cells. As it goes through this for loop three times. I'm, I come over here. I'm gonna change this to folder list, folder one, folder to folder three. This is called message list. I want to delete this reference here and change this to be called folder list just for accuracy. I showed you in another video you can come to formulas may manager in this spreadsheet, here's all the named cells that I've created. Here is message list. I can delete this reference here. Yes. And you can see this cell name has gone back to its original a eight addressed. And so now I'm going to name this two folder list. I'll go back to my split-screen view. And now we can take the same concept before we had this message box. I'm gonna change this to make directory from our other video, you may remember that this is how you create a folder in our system. And the input to the make directory function is the path where you want the folder to be created. And so I made a folder already called C Drive example. That's the folder we want our new folders to be created. And this use the ampersand, combine our strings together. We need to change this to folder list. And when we're looping before, what we're showing is that every time this loop runs three times is gonna go 123. And these cells over here. And so now it's gonna do the same thing only instead of showing a message box is going to be appending those folder names to the path that's used in our make directory function. So let's give this a try. There you go. It looks like nothing happened, but that's probably because it ran so fast. It all just happen and we didn't hit any errors. If we come over to our example folder, there we have it fold or 123. And so I'll delete these. And so you can see this run in real time. If I run this, there's are three folders based on these names. And if we wanted to get really crazy, I'm gonna come here. If I grab this bottom right corner, I can drag this down. You see Excel knows that I'm trying to increment the number there, or at least it's guessing. I can have ten folders. I want to run this loop ten times. Will show the folder here. Hit run. And there's your ten folders. And now we're really getting going on the automation. 17. Class Project 01 Description and Tutorial: Hi everyone. If you've made it this far to this video, that means you've completed all the videos I've posted so far for this class of what I consider the introduction to Excel macros and getting started from having never worked on them before. I came up with this project which will incorporate many of the concepts that we went over in the other videos for this class. And so the project that I'd like to give to you guys is that I have a list of numbers over here. And with this list of numbers, I'd like you guys to write a macro that takes this list and transposes these numbers over here on the right. But to only bring the numbers on the right that have a value that is greater than ten. And in here you'll also have some other things, such as starting with the fact that this is an XLS file type. And you can go over the instructions at the top to give you some goals to go by and some hints as to what to do. I'll also ask you to create some buttons to run your macro to perform the task. And the also clear the list so you can run the task again. And then when the macros done running to pop up a message box that will let you know that the macro is complete. So that's the project for you guys. I'll attach this file to the class, and so good luck with it and leave any comments and the conversation for the class if you have any issues or questions along the way. And so what I was going to do is I was going to complete this assignment so I can post it as the answers for you guys if you need to follow along or need to cross-check. And I figured that I just film it while I complete this project. So if you guys don't want to cheat, you can stop this video now and go straight to the project. So now we'll go ahead and try to accomplish this project. I'm gonna come over to the Developer tab, go to Visual Basic. There's no module in here yet. And so in the class We actually had a module created from recording our first macro. Without that, I can right-click, go to Insert, go to Modules. And here you go, we have our code editor, which you guys, you've recognized them, the classes. I'm going to create a subroutine. I'm going to call it sort list. There's the subroutine. And so I want to reference this list. And I don't have a name for this cell, as we mentioned in our classes, we can reference this cell is just before teen, but I actually want to give it a name to give it a more decisive reference. So I'm going to call this number list. Just to prove that that works, I'm going to type value here. I like to use this message box that's just, just to check on my code. If I run this, there you go. We have number list, but we don't actually want to use that cell. We want to loop through each value of these cells over here. And so I'm gonna type offset. I'm just going to type one for now. And I'll run this again. And here we go. We have number nine, which is the first item in our list, because we are one down from the number list cell. Now I know that I'm going to want to loop through this list here because I'm going to want to check each value to see if I should move it over to the list on the right. So for i equals and I want to see how many times I need to run this loop. If I highlight this, there's a little Excel trick here. If you highlight this on the bottom, it gives you some basic facts about what you've highlighted. So the count is ten, so I'm going to want to run this ten times. I equals one to ten. So I'm setting up a loop that's going to increment i ten times. I'll tab this out and I will set an endpoint for the loop next, ie. And let's try this out. Nine. So I see the mistake I made here is showing a message box showing nine over and over. And I think this is going to run ten times because that's how many times the loop is going to run. So let's click through this. And that's because I left. This offset is one. And really I want this to be i. And so the value of i will change every time this loop increments the variable I. And so if I run this again, here's 9118 and they go, I can hit enter here. And you see we incremented a loop that goes through each one of these cells. Next, we know that we want to put our results over here. And so just like with the number list, I want to create an absolute reference for this cell. I want to give this cell a name. And so I'm going to call it results. And so just like we were able to use the offset function to reference these cells going down. We can do the same thing with our results list over here. Let me get a little more room here so we can view our code better. And so I can reference this cell right here, for example, by referencing range results. That offset in just like over here where we use the variable i to increment the list downwards, I can use I again, dot value. And so now we're talking about the value of this cell here. And on the second time the loop runs, i will be two and we'll be talking about this cell down here. And so just an example will make the value of the cell i. And so what I expect to happen is every time this loop runs, i is going to go from 1234. And that will also offset the rose down. So you'll have one through ten down this list, right? So we're going to open this up a little bit. Then let's test out this code here. And so we're going to hit Run. And there you go. You see that as this loop increments, it is placing the value of i as it goes through the loop into this list over here, because we are offsetting from the results cell, which is the top of this list. Now, we don't actually want these numbers in here. What we wanna do is be moving these numbers from this list over to this right list if the value is greater than ten. So let me create a little bit more room here. And so as a stepping stone, I'm not going to bother trying to figure out whether the number is greater than ten. Let's just add this code and we'll update it so that we just move these numbers over to the right list. We won't bother checking whether it's greater than ten. As I mentioned in my classes, I like to test the logic app a little bit at a time rather than just going straight for the final product. And so here we know we are setting the value of the list on the right, instead of setting it as just I, we know we're getting the value. We got an error there because this is not a valid line of code. We know where giving a message box where we have the value of the number list over here on the left. Here we're setting the value of the list on the right. And so if I combine these two, there we go. Now, the list on the right will just match the list on the left over here. And we're not going to bother showing this in the message box anymore. So I'm just going to comment that out. I can delete it later. And so let's try running this and see what happens. Alright, so you guys might be able to see the problem I'm having just as I'm sharing these windows left and right, my icons are disappearing up here. And that's just because I'm trying to see everything on the left and I'm scrunching everything up on the right. So what I'm gonna do is I'm actually going to delete these columns over here. And we'll just go from memory because we know what we're trying to do here so that I can show you guys everything on the same screen. So here I make this window wider. I see your play button again. Let's run this. And there we go as we loop through each row or simply placing the value of this cell into the cell on the right, and it goes down this list back and forth. And now we want to combine the if statement. So we have this code executing, which brings values from the left to the right, but we only wanna do that under certain conditions. And so I can create an if statement. Where. And again, I'm getting error messages because I'm leaving incomplete lines of code here. I can grab this, which is the value of the item on the left. And say, if we're grabbing this, I'm going to copy and paste it up here. And we want to say if the value of this number on the left is less than ten, close the condition. Then we're going to perform a line of code which is moving the number over to the list on the right. And we're going to end that with an endif. And so will clear that we'll try running this. And there you go. It looks like we have a successful macro here. But one thing we wanna do to clean this up, this is fairly long. And so I'm writing this statement twice here. And you don't really need to do this, but to practice one of the concepts we learned in our class. We can create a variable to hold this value. And then we can use that in these two places instead of the full statement of the range, offset and value. And so we'll declare a variable dimension. We'll call that variable number. And we'll call that an integer because none of these have decimal points. We'll call number equals. And we'll go to range, offset and value. Which is the number that's in each of these cells. And actually one thing I can do here, because this loop is going to run ten times. I don't want to declare this variable ten times. I only need to declare once. And so I can move it outside of the loop here. And now numbers declared only once, but each time this loop runs, the value of number will be reassigned to each item on this list as i increments through the list. And so these references here is the same as this. So instead of writing the full range reference, I can just write the variable number. And I can change that in these two places over here. And so let me clear this list. I hit play. And there you go. You see the same code worked only we simplified it with the use of this variable, the variable name, number. So that basically completes our macro. And so now we'll just tidy up with the other bullet points on this project. So we want to save this from an XLS file type, which is the default Excel file type. And we want to make that a Macro Enabled Workbook. And so we're gonna go to Save, Save As. And we'll change this to a Macro Enabled Workbook. And there you go. And now when I press Save, I don't get an error message telling me that my macro is going to be lost because of the file type. Next bullet is generating the macro. That's what we've already completed. We want to create a button that run this macro so that we don't have to keep going. The code editor and hitting play. In other way we could run this macro is clicking macros in running our sort list macro. But to make it even simpler than that is, we want to go to Insert, we're going to add a button here. We're gonna create that button. The macro we're gonna run is sort lists. But actually I want to show you guys, I can click OK here and that's going to make this button run the sort list macro. But if I want to change that, I can right-click this button, go to assign macro, and then I can change it anytime two will go to sort lists, click OK. We can change the text of the button to sort lists. And we'll try this out. I'll clear this, highlight the leet sort list, and there you go. Now we're running the macro from a button. And then the last bullet we want to do is to create a clear button. And so I have a little typo over here. I'll change that. We're going to add another button, will go to Insert button here. And this is why I showed you before that you could assign the macro later. We do not have a clear list macro yet. So I'm just going to click Cancel. It still creates the button but doesn't assign any macro. I can click this and it doesn't do anything. Will change this text to clear. And we still need a macro that's going to clear this list. And so we could go into the code editor and type out a new subroutine here. But what do we really quick and easy is if we just record a macro. So I'll come up here, I'll click the Record Macro button. It defaults to being named macro one, but I can change this to clear list as our new macro name. I'll click OK. I can take this list, highlight it, hit delete, which clears a list. Will stop recording. Now if we go to this Clear button, I right-click this, go to assign macro. Here we have our new recorded macro named clear list. I'll click OK and that'll assign the button to the macro. And if I run this again, there's our sorted list. It clear. And now the list is cleared. Sort list. Clear the list. Alright guys, so there you have it. There's the tutorial on how to complete our project. Hope you guys enjoyed this class. Please leave any comments and the conversation. Let me know any questions you guys might have as you're going through the class. Or any excel or Excel macro questions that you guys might have on any other topics. And I'd be happy to answer them. And it'll give me new ideas for more classes to create. Thanks a lot. Appreciate you guys taking the time. 18. Moving Data - Create Form Record Macro: Since we're going to do a tutorial on moving data, I want to create a simple use case for a template on a reason that we're going to need to move data around. So what I'm going to create here is a purchase form. So I'm just going to draft up something to start with and then we're going to start to evolve it through the course of this project. So first I'm going to create a very basic form here. Will create item, quantity and price for now until we think of more things to add to it. We're going to give it the look of a form here. I'm hitting Control C to copy control V to paste. If I hold Control, you can select multiple cells at a time. Wherever you click. And then I'm going to create a border here. Now let's create a little data table for this data to go. And I like to use the default formatting out of Excel just to make this look a little better, but not have to worry too much about making selections here. And so now we have a basic user form. Let's start to fill it out. So let's say we have one pencil here. We're going to order ten pieces of them. And for a price of $1. We'll turn this into a dollar sign here. And so here now we just have the framework for the template. We're going to start out with. The first thing I wanted to do is just to show you the basics of recording a macro, which we went over in our last lesson. But we can use this Developer tab. Go to Record Macro, will click Record Macro here. We'll just leave it as the default name. And now we are recording whatever we do on the screen. So I'm going to click into the item cell, do Control C, paste control V. And I'll just do this for each of these fields. And we have moved data from our form into our table, won't even call this the purchase table. Now, if we go to stop recording, we have a macro-level coordinate background. To view the macro we just recorded. We can click this Visual Basic button. And module 1 is the default module that was created for the recording. And then here's macro one. I just show you another way you can get to this is you can click this macro buttons, which shows you all the macros that are in the Excel application that's open right now. You can run the macro or you can click Edit. And if you click Edit, it'll take you to the code here. Let's take a look at this macro that we created. Let's try running it. And so I'm just going to split these screens up so we can see them a little better. I'll just clear out this data here. Without even looking at the source code. We can go to Macros and we have macro one and let's click Run. And you can see all the data from the form went into our table over here. So let's clear this out. Now that we have our code pulled up here, you can hit F8 to step through each line of code one-by-one. And so if I'm highlighted, my cursor is in this macro. I hit F8, and then I can step through each line and we can see what's going on here. We're going to select range A5. So that's the cell address, a five over here. We're going to copy it. We're going to select I3 over here. And then we're going to paste it. And then it's just going to do that over and over again for each of the three fields. And then you can see it even recorded when I typed in the header of the table purchase table here. And so one thing is we just go through this tutorial. I'm gonna make this a little more convenient to test out. I'm going to add a button over here to our form. Well actually I'm assigning the macro here, so I'm going to assign it to macro one for now will have to change that later as we change the names of our macros, we're going to assign it to macro one. And we'll call this submit. And now if I clear the data out and hit Submit, you can see our macro runs. 19. Moving Data - Name the Ranges: I mentioned in an earlier lesson that I don't like to use cell addresses when making macros. And by that, I mean how this macro, when we recorded it, used cell addresses like a five and C5 here, and it pastes them to i3 and J3. And the reason I don't like that is for many different reasons. But the most obvious one is if I do something like cut and paste this table somewhere else or reposition it. And then I run this macro again. You can see that it still uses these absolute cell addresses in here and nothing works anymore, or at least doesn't work correctly. We have to go into this macro and we update the cell addresses again. And the problem is then we could just move the table again and we're constantly evolving these tools as we're going. And so this requires constant maintenance and updates. So what we're gonna do here is we're going to clean this up a little bit and then change this. So we're not using the cell addresses and instead giving names to the range that will move around with these cells. And that'll make more sense as we go through this example. So one thing I'm going to do now is just remove this part where we create the table name because we accidentally record that we didn't really mean to. And so we're going to still stick with this concept for now. We're going to just keep growing on this concept and changing it to become more advanced as we go. So we have cell A5 here, and I don't want to use a five because of the problem we mentioned. Instead, I'm going to give this cell a name and you'll remember this if you watch through our earlier lessons here, I'm going to call this cell, actually I'll call it form item. And then quantity. I'll just keep the same idea. And I can just type it in this top-left address field here. And actually I want this to be formed quantity and form price. And you can confirm that this worked correctly is when you click over these fields, you can see the name of the cell doesn't say A5, C5 anymore. It gives the name the range. And I can confirm this even further by going to Formulas Name Manager, and you'll see the names I created for each of these fields. And so I can just show that this now works for our form even though we haven't handled the table yet. So if I did something like cut and paste is move these down. I'll just copy these formats to fix that. Then this original macro says A5. Actually don't want this to say A5 anymore. I want this to say form. Pencil. Again, C5 here is now foreign quantity 8, which was our price, is formed price. And so let's run this. We have an error here probably because I made a typo somewhere. Range form, pencil select. And this is called Forum item. If you're watching and following along, you may have noticed that as I was typing this and we'll run this. And you see it was able to find our source data over here in our form even though we had moved it. And you can see we can move it right back to where it was up here. It's actually one row higher. And we can run this again. And the macro confined source data because we gave it a name and this name moves around with our cut and paste. It'll also move around if we do something like insert a row, you can see the formats duplicating here. I'm just copying and pasting these generic cells to fix it. And I'm copying and pasting blank cells over here to clear those out and hit submit. And you can see it's working again. One other small thing you might notice as a side note is that the format of the source cells, because we're using a control C copy and paste when we record the macro is bringing over the formatting, which includes the borders of the cells also. And so let's do this for the table also. So we stop pasting our data up here in our original recording. This is going to be a little trickier. The more obvious thing that we could do here is to just name each of these destination cells in the table. But that's going to cause us to have a lot of names. So imagine this was a 20 column table, or the rows are going to keep going farther down as we gather data. You don't want to give names to all of those things. So I'm going to do something a little different here. Instead, I'm going to choose the top left field. Usually I choose the first column and then take the header of that as the top-left, what I call the anchor point. And I'm going to give that a name instead. So I'm going to call this table purchases. And so now this cell here is called table purchases and this is an anchor. And I can reference these other cells based on this anchor as long as this table is always moving around with this anchor cell. I'll show you what I mean here. Here where our item is going into cell I3. In this case, we really wanted to move that this part of the table and the table is down here. Now, we did create a name for our anchor cell table purchases. So instead of I3, which is where the information is ending up when we recorded our macro, we're going to choose our anchor cell, which is table purchases. And then we're going to use this offset function, which we also covered in our earlier tutorials, where I can tell you the row and column that I want to be offset by the cell. So if I say are real offset is one, that means it's this cell and then one cell down, and then our column offset, if it was one, it would be one cell to the right. But instead, we're gonna go right below it since we're working on item and our offset is select here. So our offset is our anchor cell down one row and write 0 columns. And we're going to select that. And just like our recording, we'll leave this for now where we paste or item. And then for quantity, our selection instead of J3 will replace this. And you can probably see where we're going with this. Our column is just going to be over one to the right. So add one there. And then k3, which is up here. We want to move that down to this cell. And again, we're going to use the anchor. So we're just gonna go over to the right two columns and down one row instead. Now we have the selection of i1. That's just when we created the table heading. And so we don't need that anymore. We're just going to delete this here. And so if we did this correctly, what we should do is take the values from each of these three cells and then paste them in each of these cells based on the anchor of the table. So let's run this. And so you see that worked. Let's try this again. So I'm going to cut and paste and move this table even farther down here, you can see our anchor cell moves with it, table purchases. And so I can clear this again now and try this again. And our data continues to move with our named ranges. And then I'm just gonna go ahead and clear this out. 20. Moving Data - Simplify Recorded Code: Now that we've updated our tool, so we have named ranges and we can move our data around and feel confident that the logic is still going to work out. What I wanna do is just clean up the basic functionality here. When we record macros, it's a bit inefficient because it recorded every action that we do. But since we're working with the source code directly, we can actually cut out some of the steps that we do when we're manually working with Excel. And so for example, when we're selecting the form item cell, which is over here and then copying it. You can see we're first selecting it and then I hit Control C and do the copy of the selection. I can actually cut this out and make these into one where I don't have to click into the cell since I can refer to the form item cell, I can just copy it directly in one line without selecting it and then saying, I want to copy whatever I have selected. And so we can cut these out. And the same thing happens for our destination, where we're going to select this cell, which is what this line says. And then in our active sheet we're going to paste in whatever So we have selected. I should be able to actually come in here and just type paste. I'm not sure if pace is actually going to work the same. So I'm just going to comment this out and check if this update works for this one item field. Let's clear this out and then we'll hit submit. And you can see paced actually did not work quite as well. Now that we've realized that for now, I'm just going to hit Undo and then leave the pasting here for now we're going to come back to this. I'd like to leave in these little tests that I'm doing in the tutorial. So you get to see the full process here. For the copy. We saw that that work and it only had a problem with the pasting. So let's run this again just to see if that fixed it. So you see this reference where we chose the form item directly ended a copy that work and we cut out some of the inefficiencies of selecting the cell first. So let's do the same thing here. So form quantity. We'll come over here. Instead of selecting it, would just say dot copy. And then we can delete this over here for the form price, we can remove the select with just the range and copy. We're just going to leave this in here. I'm not sure if we need this right now, we'll test that out. But this is the equivalent of hitting Escape when you hit copy. So you see how we have the moving dotted lines here. This is hitting Escape. So that's what got recorded here automatically when we're recording our macro. So let's try this out. Now you see we have an error here and it's saying we have an error when we're trying to pace. I'm realizing the reason for that is because we don't have anything in our copy clipboard right now. If you went back and looked at how we rearrange these lines of code when I made it more efficient, is the copy line was actually here. This is where the old copy line was with selection, that copy. And we move this and you see because this one, sorry, I didn't like that. This isn't a valid line of code here because this line is the equivalent to hitting Escape. That was similar to clicking this field hitting Control Copy, hitting Escape, and then trying to paste in here because it had escaped with this line of code, then there's nothing in the clipboard. And so what I really need to do is let's see if we even need this. So let's try this out. We can run this step-by-step so we can see what's going on here. You see we're copying or pasting copying here we see the dotted line and we hit the active sheet and we paste. We're going to hit the same problem here and you'll see what I mean. Here we have the price and the clipboard. And then when we run this line, it deselects the copy from the clipboard. And so we're going to run into an error right there, right here actually when we try to paste. And so we'll go back here. You can actually drag this arrow up to go back to where we were before. Now we have price in the clipboard again. And I think we can actually remove this because we inherently unselect our copy when we copy something else. So you don't have to do the equivalent of hitting Escape to take something out of your clipboard. We can just skip that line completely. And then we have something working here. What we're actually seeing is because we don't have that line of code that deselects our clipboard. You can see this is still active even though our macros completely finished. So if I hit undo here, we'll clean this up. We'll actually just move this till the end. And now we'll run this step-by-step just so you can actually follow along now that we're editing code manually. Now rather than just using the pre-recorded macros, you can see this custom code that we created, how it runs step-by-step. And so now I'll hit F8. We step through it, we copy item, we paste it into our cell, and we do the same thing for three fields. And then now that we're done, we hit Escape to clear out our clipboard. 21. Moving Data - Paste Values Only: Another small thing I want to go over is just because we're using a copy and paste here. You saw before that we're moving the borders over to our fields here. And that doesn't really matter to us because it looks okay in this format, but it's not always something we want. In fact, very often you're only going to want the values and not all the formatting of the source fields. So what we wanna do is do the equivalent of pasting just the values. Just to show you what could go wrong here, I'm just going to clear these cells out, including the formatting and show you that if I did something like have this cell is highlighted. When we run this macro, the entire formatting of the cell moves over here because it's the equivalent of doing a copy and paste. So I'm going to clear this out. Let's clear these again. I'll bring this back to white. What I wanna do now is basically just make this the equivalent of a copy. And then paste right-click and then paste special. There's these shortcuts here. If you've got to pay special, you'll see it actually written out and paste values. If this was highlighted. You'll see that if we did this and went to paste special and values, that only the value and not the formatting of the cell moves over. And that way we can control the formatting of this table without getting overwritten by the formatting of our source data. How are we going to do that is updating these lines of code for pasting into what is the equivalent of a pays value, rather just a typical copy and paste. I don't actually have that notation memorized. So like in my earlier tutorials, I don't want to just look that up and have a notepad over here and type it in. Instead, I want to show you the process that you can use to find information if you don't know it. And I don't show it in these tutorials. So what I'm going to do is just go over here to Google and I'm going to type VBA, paste special values only. And so if I go over here, you can find notation and these websites, I'm just browsing quickly to our answer. You can see you can take a range and then it says Paste Special and then Paste Values. And this seems to be a property of a range. So let's try this code out. This is our range over here. We have an offset on that range, but this is still considered a range. So we're going to remove this select and do a paste specials. We have an extra period here. And then with this, we could actually comment out this line because I don't think we need it anymore. So let's try this out. In fact, I'll paste it in here because that's where we have our highlighted quantity cell. And let's just try this out. I'll clear this, It's submit. And there you go. You see we paste values in here and the formatting of the highlighted cell did not get moved over. And I'll do this for the last cell here, just for the price. Actually don't have this in my clipboard anymore, so I'll copy from this other line above it. And then we can just clear these lines out. And here now you see as we're doing manual updates, our code is getting more and more efficient, which is best practice that we want to do. When we recorded the macro. It had a lot of extra steps. The more we can simplify the code itself, the easier it's going to be to maintain our code in the future. So we'll test this out one more time now that we have all our updates, you see the self self-appraisal price still has our formatting here. So I'll clear that out completely. What submit. And now we have just the values being moved in here. Price, we still want to be in dollar notation so we can make this field itself and accounting field. And it's going to maintain that formatting in the future as we add different values to it. So even if we change this to a dollar 50, we can hit Submit and you'll see it updates in it retains its accounting cell type. 22. Moving Data - Without Copy and Paste: Up until now, what we've been doing is starting with our baseline where we recorded a macro and did a copy and paste into these cells. Even this is slightly inefficient. We're recording macros, basically we're doing it the way a regular user or person sitting in front of their computer would move data around. Because we have access to the code directly in VBA. We actually don't have to run things the way humans sitting in front of the computer clicking with the mouse and keyboard would run things. So in this example, I'll show you what I mean by that. So our process here is basically copying each field in our form and then pasting it to our table. We don't actually need to do that. We don't need to do a copy and paste. And then what we've done before with a paste specials to retain our formatting. Instead, because we have access to these variables directly. We have this field over here being table purchases with the offset. Because we have this reference of this field, we can actually forget the copy and paste. This cell has a property called value, and we can just set this equal to something. And so if I set this equal to Steve here, we'll run this now. And you can see we just place the, this line of code, just place the IV directly into the cell. And I have my source data here, which is what we did a Control C copy from. We don't have to do a copy. Instead, I'll just take this source cell, which is over here. And instead of the text that I just typed right here, I'll put the value of that. And so basically what this line of code is saying, and we don't need this anymore, is that this cell over here, which is referenced to the left side of this line of code, will equal the value of this cell over here. And so this is basically the equivalent of a copy and paste only. It'll be even more efficient. So let's try to run this and you can see that the macro still works the same and the item pencil has moved over to our table. So let's do this for the rest of the code. We don't need this line anymore. Value equals the value of our source. And we can do the same thing over here. We'll cut value equals form price stop value. And this where we clear the Copy clipboard doesn't matter anymore either. And you can see we're getting much more efficient here. So you can see where the shortcomings are of recording a macro and how much more efficient working with the code directly if you know what you're doing can be. So let's show this whole thing. We'll clear this out. Hit submit. And here we have the same result as before, and now we're only down to three lines of code. And you can see the same thing still applies. The highlighted cell, the formatting did not move of this table. We're just moving the data we want. And one thing that this simple example isn't showing is as your macros get much longer, they can become pages and hundreds and thousands of lines of code. This is much more efficient. And so this could be the difference between your macro running for thirty-seconds or even multiple minutes. Or it running much more efficiently and being done within a split second or a matter of seconds. 23. Moving Data - Writing to Next Row: Now we've created a very clean macro here with just three lines of code that moves three pieces of data into this table. Next problem that you might be thinking of already is that our macro just keeps moving information from this form into the exact same fields over and over again. So to make this an actual transfer of data into a table, we're going to want to append a new row every single time we hit Submit so that we get another row of data each time we submit a new line item here through our form. There's many different ways to do this. So I'm just going to show you one way that you can do this for now. The first thing I wanna do is figure out what my next line is. And because this might fill up with data over here. And we might have more lines, we need to go through this table and figure out where our next row is going to be. How I'm going to do this is I'm going to use this anchor table. And in our earlier lessons, we went through loops. So that's what we're going to utilize to figure that out. I'm going to figure out what our next row is before we even do anything. So I'm going to start to draft up some code before these lines where we're adding our three fields at a table. One thing I'm going to utilize here is this named range, which is our anchor for the table over here. What I'm going to do is start at this cell and then work my way down this table. I'm going to do a for-loop, which we went over before. I'm going to start with just some numbers I'm going to make up here. I is 1, 2, I'm just going to say 99 thousand, just a gigantic number that I don't think our table will reach, at least not in the foreseeable future of this demo. So I'm going to say next, I, this is our notation for our for-loop. And then I'm going to choose this range or anchor for the table purchases. And I'm going to say offset i 0, that value. And so I haven't actually finished this line of code yet. I just want to show you that what I'm referencing is only our first loop, I is going to equal one. And so just like we used over here, if offset is one, that means one row down from this cell and 0 columns to the right. So we're talking about this cell over here. And then the next time this loop goes, I is going to become two. And then we're going to be talking about this cell here. And you're probably starting to get an idea of what I'm gonna do. So I'm going to wrap this in an if statement. I'm going to say if offset 1 equals blank, then I'm going to end this if statement here. But basically I'm going to put some code in here that says at end of table. And so when I equals one, there's going to be something here. And so we're not going to meet this if statement. We're just going to move on to our next loop. So we're going to skip out of this if statement. And we're gonna go to the next loop. When I equals two, we're going to be over here and then the value will be blank. And then we're going to end up inside this if statement. And then this next blank is going to be our first row. We're going to create a new variable here, and we'll call it DEM. Next index as integer. And so when I shows us an index that brings us to a blank row, we're going to say next index equals i. Now we don't need to keep running this loop because we've already hit the bottom and we don't need to run this loop 99 thousand more times. So we're just going to exit the for statement right here. Now when we're done this for loop on next index is going to be two in this case, which is really the location of our next row. And so where are we? We're always adding data at just one row down from our anchor point. We're going to use this variable next index for how many rows down from our anchor, we want to go and add the next data. And so if we change this here, now, we're just one row down, we're going to be a two when we run this. At least that's what I expect. And then we're going to be setting these values to these form values again, because it's hard to see everything. I'm just going to shrink this down a little bit. Because we create these named ranges. I can do what I just did and everything's working correctly. And so you can see this is one of the key benefits to working with these named ranges. Because it's the first time we're going to run it. I'm actually going to use F8 and step through this code and hopefully the concept works just as I described it. And so we're going through the loop here. I equals 1. We can highlight over these variables just to see their value. The value of one row down from the anchor is not blank. So you see we jumped out of this if statement. We did not enter into it. And then we're gonna go through the next loop where I equals 2. We're talking about this cell here, and the value is blank and silver in next index equals two. We leave the for loop. And now our next index is two. And now we're going to set the value of our anchored down to rows. And it looks like it worked great. Just to prove it. We're going to change this up a little bit. I know these example prices might not make any sense. So bear with me if you're thinking to yourself that this isn't what a pencil costs. Hit submit. And you see are updated. Macro works great and we can keep adding information to this table here. 24. Moving Data - Add Timestamp: There's one feature that I want to add, this tool that we're creating, just because it's a very basic feature that would typically be part of a form to database tool like this. And that is a timestamp on when we are adding this information. And we have this functionality we built up already where we can keep adding lines in this table. And I want to stamp each record with the time that we added this row to the table. That information isn't in the purchase form and we don't want to ask a user to type in the date and time every single time they are submitting a new line. We don't want them to do that themselves. And so we'll just do this right in the code over here. We can use the same concept that we have over here, where we have our anchor. We have our index for how many rows to go down to find our next row. But our column is now our third column over from our index. So 123 is our timestamp. And instead of taking the value of a named range from our form, which we already have over here. We can just use a VBA function and we'll use the function now, which is a timestamp. And so let's add a row here. You'll see we have a timestamp value that's now shown up. We'll make this a little bit bigger just so we can view it. Actually, we can make this quantity much smaller also to give us some room here. And you can see as we are adding these rows, we now have a timestamp in here also. 25. Moving Data - Writing to Different Sheet: Next thing I wanna do is split this information up. And that's gonna give us a chance to see how we can move data between two different sheets. So I'm going to create a new sheet here because very often we wouldn't have a user-friendly form like this right next to the data table itself. So instead, these might be in two different locations. So I'm going to cut and paste our table, and I'm going to paste it over here into another sheet. You can see because I use Control X for cut and paste, our named range actually moves with it. And so you see over here, where would have been our anchor cell? That name moved with it over here to this new sheet that we created on that alone. Let's see if this works. So I'm going to change the quantity here. I'll change this to paperclip. I hit Submit. So you can see here that we didn't need to do anything because we had named our ranges and table purchases moved over here. This sheet actually knew the table purchases was on a different sheet and was able to find it even though we had moved it. One thing we can do here is we're going to write form. And over here we'll call it table data. And we can keep adding information over this table and you can see it continues to work and we have our new timestamps. And even though this work, I just want to show you that it's always better to be more explicit in how you're writing your code. Just because in different situations we just haven't hit it yet. But this code could get confused because we have two different sheets and it might not know what information we're trying to reference. This isn't a problem yet, but something to be more explicit could be adding a sheep call out. And we'll say from table data, range table purchases. So now we're telling R code that the range table purchases is very specifically in the sheets called table data. And we could do this for all the rest of them. I'm not gonna do it right now, but this would be more explicit. So you're telling the code exactly what's going on. And we can try this again just to make sure that it's still working. So you see in this case, this information right here, it wasn't needed, but adding it doesn't hurt. So in different situations, this may work better and even be needed for everything to work correctly. While we're on that topic, I actually want to just come up with an example to show you when the Shi call-outs need to be specified. The way I'm going to do that is to take this data right here. I'm going to clear this out. I'm going to comment out this line. And instead of writing our item, we're going to fix this all back later. But just as this demo, instead of writing our item over to the next row of our item column, I'm just going to put this in cell A1. And you're going to see that we're writing a one. And we ended up changing our purchasing form over here to say paperclip, because this form does not know that A1 means table data. In fact, I didn't even bring that up when I change this code. And so you can see A1 is on different worksheets. And if I make a new worksheet and I want to write to a one on sheet 1. I'm going to have to specify directly. And so I'm going to change this back to purchase form just to fix the mistake we made over here. And then if I want to write two sheets called Sheet 3, that range A1, that's what I need to call out specifically in order to move that data over here to sheet 3.