VBA Tutorial P1 – How to create a simple VBA macro?

Welcome to the first tutorial were we’re actually going to do some coding, so let’s step into it!

We’re going to create a simple copy-paste macro from an existing file to several new files.

Oh, before we do anything, please make sure you save all your work you did so far in Excel and close all the files because Excel has the habit of restarting itself when it takes a heavy load (and that’s why I prefer python instead).

Once you’re done, open an empty Excel file. Let’s add some dummy data so we have something to work with.

Let’s add some names in column A: Sally, Brandon, Rob, Tim, Rebecca, Joan. Let’s say these are your clients. Each of these clients have a corresponding status: good client or bad client. So, we’re going to add this in column B: Good, Good, Bad, Good, Bad, Good.

As you can see in the image above, the first row has a header (Client and Status). This is something useful when we’re going to develop the macro.

As a requirement, let’s say you need to split this file in two: a file with the good clients and a file with the bad clients. Now, the coding part!

In that Excel file, you need to enter the place where you can actually write the code. To do this, hit the keys ALTand F11. Now you should see something similar as one of the two images below (the difference is done by the setting <Toggle Folders>).

Note: If your image doesn’t match any of the above, then it’s because you have the Project Explorer off. You just need to go to View -> Project Explorer.

Most of macros go into modules, so you just need to create a new module so you can write the code. To do this, you simply need to go to Insert -> Module and you should now have Module1 (similar with the image below).

Notes: Every new Excel file has two things: The Workbook (meaning the actual file) and One Sheet (the sheet you are actually adding the data and stuff). These are called Microsoft Excel Objects. Besides them, we also have the Modules where we are actually writing the macros (in one module can be added multiple macros, not only one). Moreover, as it can be seen in the image in the left, we can also have Forms if we are using any and Class Modules, again, if we are using any.


Notes: Every new Excel file has two things: The Workbook (meaning the actual file) and One Sheet (the sheet you are actually adding the data and stuff). These are called Microsoft Excel Objects. Besides them, we also have the Modules where we are actually writing the macros (in one module can be added multiple macros, not only one). Moreover, as it can be seen in the image in the left, we can also have Forms if we are using any and Class Modules, again, if we are using any.


Now, we just need to create a blank macro in the module we just created (Module1) and add the required functionalities.

Our macro will be named my_copy_macro. A macro is initialized with <Sub> followed by the name of that macro and ended with <End Sub> (ps: <Sub> comes from <Subroutine>).

If you remember from the requirements, we need to split this data into two separate files: one with good clients and one with bad client. Now, we just need to put this into code.

One thing you must understand when it comes to coding in general is that there are multiple ways of doing the same thing. Just as 5 + 5 = 10 and 22 / 2 – 1 = 10. The result is the same. Only the method is different.

Firstly, we need to set the source and the destination of our data. In our case, the source is the blank workbook we created with the sheet where we added the dummy data. For destination, we need two new workbooks, one for good and one for bad, each having its own sheets. Here is how we put this into code:

'Setting initial workbook (the one with the data)
Set wb = ThisWorkbook
'Setting initial worksheet
Set wsO = wb.Worksheets("Sheet1")

'Setting <good> workbook
Set goodWB = Workbooks.Add
'Setting the worksheet in the <good workbook> where to add the data
Set wsGood = goodWB.Worksheets("Sheet1")

'Setting <bad> workbook
Set badWB = Workbooks.Add
'Setting the worksheet in the <bad workbook> where to add the data
Set ws.Bad = badWB.Worksheets("Sheet1")

After we set the source and the destination, we need to be even more specific with our data. Our data is in columns A and B and the status is in column B. Also, we’re goung to put some data in new workbooks and worksheets, and this means that the new worksheets don’t have any headers. Let’s copy the headers from our original worksheets to the new worksheets:

Remember: Our headers are located in the first row in columns A and B.

'Copying the header to <good workbook>
wsO.Range("A1").Copy wsGood.Range("A1")
wsO.Range("B1").Copy wsGood.Range("B1")

'Copying the header to <bad workbook>
wsO.Range("A1").Copy wsBad.Range("A1")
wsO.Range("B1").Copy wsBad.Range("B1")

To decide which client goes in which workbook, we need to check the status in column B.

So our statuses are in column B, starting with the row 2 (in row 1 we have the column name; e.g. <Status>) where we have the first status, ending with the row 7 where we have the last status.

With this in mind, our range would be as follows:

Set initialRange = wsO.Range(“B2:B7”)

If we want it more dynamic, we need to calculate the last status in column B. We can do this with the following line:

LastStatus = wsO.Range(“B” & wsO.Rows.Count).End(xlUp).Row

In this case <LastStatus> value will be 7, therefore we’re going to replace it in the initialRange so we have a dynamic macro, as below:

Set initialRange = wsO.Range(“B2:B” & LastStatus)

Now we need to look at every status in our range and to add it in one of the two workbooks.

To make this happen, we need a loop through all the statuses. This is done with the line <For each> single line in the range we created. The loop ends with <Next> single line. After all lines are analyzed, the loop ends.

For each Status in initialRange
	If Status = “Good” Then
		‘copy to worksheet Good
	ElseIf Status = “Bad” Then
		‘copy to worksheet Bad
	End If
Next Status

To execute the actual copy-paste function, we need to know exactly where we want to put the information. We need to make sure we don’t override any information. To make this happen, we’re again going to calculate the last completed line, but in this case, it is for the worksheets from workbooks <Good> and <Bad>. We can either calculate it for columns A or B because they have the same length, but for the sake of simplicity, we’re going to calculate it for column B, as seen below:

LastGood = wsGood.Range("B" & wsGood.Rows.Count).End(xlUp).Row
LastBad = wsBad.Range("B" & wsBad.Rows.Count).End(xlUp).Row

Even though we are looping through statuses, the Status is only a value, not the exact location of the value (e.g. cell B2 in worksheet). We can calculate it, but again, for the sake of simplicity, we’re going to do it another way. We’re going to initialize a variable with the value of the row we are having our first status (e.g. 2) and we’re going to add 1 when going to the next status, as below:

locationValue = 2
For each Status in initialRange
	If Status = “Good” Then
		‘copy to worksheet Good
	ElseIf Status = “Bad” Then
		‘copy to worksheet Bad
	End If
locationValue = locationValue + 1
Next Status

Now we are ready to copy the data. We just need to specify the copy location and paste location for each check of the status, together with the calculation of each last row so we know where to paste the new data:

locationValue = 2

For Each Status In initialRange

    If Status = "Good" Then
    
        'Calculating the last used row in the <good workbook, worksheet Sheet1>
        LastGood = wsGood.Range("B" & wsGood.Rows.Count).End(xlUp).Row
        'Going to the next empty row
        LastGood = LastGood + 1
        
        'Copying the name
        wsO.Range("A" & locationValue).Copy wsGood.Range("A" & LastGood)
        
        'Copying the status
        wsO.Range("B" & locationValue).Copy wsGood.Range("B" & LastGood)
    
    ElseIf Status = "Bad" Then
    
        'Calculating the last used row in the <bad workbook, worksheet Sheet1>
        LastBad = wsBad.Range("B" & wsBad.Rows.Count).End(xlUp).Row
        'Going to the next empty row
        LastBad = LastBad + 1
        
        'Copying the name
        wsO.Range("A" & locationValue).Copy wsBad.Range("A" & LastBad)
        
        'Copying the status
        wsO.Range("B" & locationValue).Copy wsBad.Range("B" & LastBad)
    
    End If
    
    locationValue = locationValue + 1

Next Status

You probably noticed that the new workbooks don’t have the names Good and Bad. This is because we haven’t saved them just yet. For saving the files, we need to specify the folder where we want each file to be saved. The easiest location is the <Desktop>. However, the actual path is different from one user to another. To make this dynamic, we need to get the username that is logged in, using the following line:

Username = Environ(“username”)

Now, let’s add the Username into the Desktop path:

desktopPath = “C:\Users\” & Username & ”\Desktop”

Okay! Let’s add the lines for saving the file:

goodWB.SaveAs Filename:= desktopPath & “\Good.xlsx”
badWB.SaveAs Filename:= desktopPath & “\Bad.xlsx”

After saving, it would be great if we close the files so we don’t keep the open as it is useless:

goodWB.Close
badWB.Close

In the end, we’re adding an alert to let us know when the macro finished running:

MsgBox "Macro Finished"

To run the macro, you need to go to View -> Macros -> View Macros in your main Excel file, the one that has the original data (most likely named as <Book1>). In the newly opened window, click on the macro name we set up earlier (e.g. my_copy_macro), click on Run and wait for the message <Macro Finished>.

Now go to your Desktop and you should have two new files: Good.xlsx and Bad.xlsx.

Note*: If you run the macro for a second time without deleting the files already created, you will receive two alerts from Excel asking you if you want to override the existing files or not (I’ll show you how you can get rid of this in another tutorial).

If you plan using this macro another time, you need to save the original file (e.g.  <Book1>) as Excel Macro-Enabled Workbook (e.g. .xlsm). You can save it wherever you want, you just need to remember that location so you can use it another time.

I hope you will find this tutorial useful! Full code below!

Next post -> VBA Tutorial P2 – What is Selenium and how can you use it?

Other posts in this series -> VBA Tutorial for Beginners


PS: If you want to support this website, you can do it through the Show Your Support page!


Sub my_copy_macro()

'Setting initial workbook (the one with the data)
Set wb = ThisWorkbook
'Setting initial worksheet
Set wsO = wb.Worksheets("Sheet1")

'Setting <good> workbook
Set goodWB = Workbooks.Add
'Setting the worksheet in the <good workbook> where to add the data
Set wsGood = goodWB.Worksheets("Sheet1")

'Setting <bad> workbook
Set badWB = Workbooks.Add
'Setting the worksheet in the <bad workbook> where to add the data
Set wsBad = badWB.Worksheets("Sheet1")

'Copying the header to <good workbook>
wsO.Range("A1").Copy wsGood.Range("A1")
wsO.Range("B1").Copy wsGood.Range("B1")

'Copying the header to <bad workbook>
wsO.Range("A1").Copy wsBad.Range("A1")
wsO.Range("B1").Copy wsBad.Range("B1")

'Deciding the last row which has a status
LastStatus = wsO.Range("B" & wsO.Rows.Count).End(xlUp).Row

'Initialising the range where the status data exists
Set initialRange = wsO.Range("B2:B" & LastStatus)

'Initializing the first row which has the data
locationValue = 2

For Each Status In initialRange

    If Status = "Good" Then
    
        'Calculating the last used row in the <good workbook, worksheet Sheet1>
        LastGood = wsGood.Range("B" & wsGood.Rows.Count).End(xlUp).Row
        'Going to the next empty row
        LastGood = LastGood + 1
        
        'Copying the name
        wsO.Range("A" & locationValue).Copy wsGood.Range("A" & LastGood)
        
        'Copying the status
        wsO.Range("B" & locationValue).Copy wsGood.Range("B" & LastGood)
    
    ElseIf Status = "Bad" Then
    
        'Calculating the last used row in the <bad workbook, worksheet Sheet1>
        LastBad = wsBad.Range("B" & wsBad.Rows.Count).End(xlUp).Row
        'Going to the next empty row
        LastBad = LastBad + 1
        
        'Copying the name
        wsO.Range("A" & locationValue).Copy wsBad.Range("A" & LastBad)
        
        'Copying the status
        wsO.Range("B" & locationValue).Copy wsBad.Range("B" & LastBad)
    
    End If
    
    'Going to the nextrow which has the data
    locationValue = locationValue + 1

Next Status

'Getting the username of the user logged in
UserName = Environ("username")

'Creating the dynamic Desktop path
desktopPath = "C:\Users\" & UserName & "\Desktop"

'Saving the <good workbook> to desktopPath with the name <Good.xlsx>
goodWB.SaveAs Filename:=desktopPath & "\Good.xlsx"
'Saving the <bad workbook> to desktopPath with the name <Bad.xlsx>
badWB.SaveAs Filename:=desktopPath & "\Bad.xlsx"

'Closing the saved files
goodWB.Close
badWB.Close

'Adding an alert to let us know when the macro finished running
MsgBox "Macro Finished"

End Sub

Leave a Reply