Home

Learn Excel VBA Programming through building practical code. In this tutorial, we will automate an end-to-end real world reporting process. Concepts will be covered from scratch. By the end, you will have gained Beginner Level knowledge of Excel VBA.

No prior knowledge of VBA is required. However, you do need some working knowledge of Excel.

Intro

VBA is a great tool to enhance productivity of any corporate analyst who spends a great portion of their time in Excel. It allows you to automate repetitive tasks, giving you options you might not have thought possible before.

This blogpost is designed to support the above Youtube video. The video contains detailed explanation and walk through. This webpage provides a summary of the basic concepts as well as a commentary on the entire code. You can also, find the full, uninterrupted code for all the macros shown in the video (at the bottom of this page – refer to the Menus).

Resources

All the completed macro files, source data and macro template are available to download from Github.

Scenario

It is the end of the accounting month. We have downloaded the full month’s sales numbers for all our stores into an Excel file. This report is in raw format, probably out of an ERP.

As an analyst, we need to copy the data from this file and paste it into our report template file. This template file has a lot of formulas and there is also, a table summarizing the sales by region and product category.

Next, we need to save a copy of this template with only the relevant worksheets and without any formulas.

And finally, we will email the copy to the rest of our team.

Automation Approach

We will first build a simple 3 step process that will need to be triggered manually. The 3 steps will be:

• Import Data
• Create Report Copy
• Send Email

There are two bonus sections

1. Error Handling

• Detect user or data related errors before Importing Data.

2. 100% End-to-End Automation

• Run full process, including error handling, in just one step.

Setup VB Editor

To access Standard toolbar, go to View in the Menu, then go to Toolbars and select Standard.

To access Project Explorer, go to View and select Project Explorer. Our code will be held inside a Module in this section. To create a Module, right click in the Project Explorer, select Insert and select Module.

To access Properties Window, go to View and select Properties Window.

The Immediate Window is a great tool for testing code from our macro or in other words, debugging. If you can’t see this window, go to View and select Immediate window.

Create a new module to access the Coding window. In my window, you’ll see Option Explicit written on top. It forces us to declare variables. To set is as a default, go to Tools -> Options -> Editor Tab and check Require Variable Declaration. (Optional) In the same Editor Tab, uncheck Auto Syntax Check. This will stop VBA notifying you each time you make a syntax error in the code.

First Macro

We will write our macro inside a sub procedure, which is like a container of code. The sub procedure or “sub” starts with the word Sub and ends with the words End Sub. In our first macro, we will display the a message in Excel using Msgbox command.

Sub HelloWorldMacro()
MsgBox "Hello World"
End Sub

To run the macro, keep the cursor in the sub and hit the play button.

Objects: Workbook, Worksheet and Range

Excel follows a hierarchy and each element within that hierarchy is called an object.

The most common hierarchy used in Excel VBA is:

Excel Application -> Workbook -> Worksheet -> Range

Where, Workbook is the Excel file, Worksheet is our sheets and Range is a cell or a group of cells.

To refer to a Range or Worksheet, we need to follow this hierarchy. Application need not be mentioned. Hence, we will ignore it here. Below examples show us how to refer to cell C2.

When referring to an external workbook:

Workbooks("Monthly Analysis.xlsb").Sheets("Data").Range("C2").Value

When referring to the current workbook: (i.e. the file from which the macro is being called)

ThisWorkbook.Sheets("Data").Range("C2").Value

As part of this project, we will refer to the Outlook hierarchy as well.

Outlook Application -> Outlook Email

Properties and Methods

Let’s consider this Cell C2 which is a Range object.

It has a value of 720, background color is Yellow, the row is of a certain height, the font is black etc. These are all its characteristics which are called properties of the object.

But, we can also, do something with it. We can copy its value or we can clear its contents. Both of these are methods. To put a definition to it, method is some action that we want to perform with the object.

Variables

A variable allows us to store the value of an object or the object itself for use later.

There are 2 reasons why I use variables. One is to avoid repeating code. And second, and the most important reason is to improve readability.

Variable to store a value

A value can be e.g. the value inside a cell or the number of rows in a range. We would normally, do this to help read the code better. To use a value variable

• Declare it using Dim statement.
• Give name (no spaces)
• Specify it a meaningful data type as String (Text) or Long (Number). There are many more types.
• Assign it a values.

e.g. We are storing our folder path in cell G2. Lets assign a variable to refer to it.

Dim mainFolder as String
mainFolder = thisworkbook.Sheets("Console").Range("G1").Value

Variable to store an object

We can also, assign variable to an object such as workbook, worksheet or range. We would normally, do this to avoid repeating code.

To use an object variable

• Declare it using Dim statement.
• Give it a meaningful name (no spaces)
• Specify data type as the VBA Object it is going to be used to store.
• Assign the object to it using Set statement.

e.g. Let’s assign a variable to store our Data worksheet object.

Dim wsData as Worksheet
Set wsData = ThisWorkbook.Sheets("Data")

Now, to select a cell D6 in the Data worksheet we can directly use this variable.

wsData.range(“D6”).select

Macro 1: Import File

Full uninterrupted code is at the bottom.

The objective of this macro is to open the source file from the folder given in Cell G1 (Sheet: Console) and with the file name given in Cell G2 (Sheet: Console), copy the data from it and place in into our existing workbook, into sheet Data within columns A and E. There are formulas in Col F onwards. These need to remain intact. If the data rows are greater than the number of rows with formulas in them, we will drag the formulas all the way to the last row.

Create a new module. Rename it m01Import.

Create a new sub.

Sub ImportData()
End Sub

Declare and assign OBJECT variables for the worksheets we will be using.

Dim wsCons As Worksheet, wsData As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsData = ThisWorkbook.Sheets("Data")

Declare and assign VALUE variables for the values in cell G1 and G2.

Dim mainFolder As String, importFile As String
mainFolder = wsCons.Range("G1").Value
importFile = wsCons.Range("G2").Value

To open the source file, we need the full file path i.e. mainFolder joined to “\” joined to importFile.

Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

Clear columns A to E in the Data worksheet to remove any previous contents. We will use the Columns object which belongs to Worksheet Object. And to refer to continuous columns, separate first and last column name with “:”.

wsData.Columns("A:E").ClearContents

Code to open a workbook is Workbooks.Open() and provide the full file path fullSourceFileName. Since we need to interact with this workbook, once its open (.e.g. copy data from it), we will assign it an Object variable.

Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

In Excel, to select all data in a worksheet, we can type Ctrl + A. We can do the same thing in VBA using CurrentRegion property of the range object. And we will assign it a range Object variable.

Dim rngToCopy As Range
Set rngToCopy = wb.Sheets("sheet1").Range("A1").CurrentRegion

To copy the data, use the Copy method of the Range object. And specify the first cell of the area you want to paste the data into.

rngToCopy.Copy wsData.Range("A1")
Close the source file.
wb.Close savechanges:=False

Now, that data is copied over into columns A to E in the Data sheet, we need to make sure that the formulas in column F onwards drag all the way down. In Excel, you could select the top row with formulas, then drag selection to the last row and press Ctrl + D. Formulas will get copied till the last row. We can do the same thing in Excel using Autofill method of the Range object.

First, determine the last row by counting the number of rows in the Current Region of the Data sheet.

Dim lrowData As Long
lrowData = wsData.Range("A1").CurrentRegion.Rows.Count

Check out this blog to find out more ways to find the last row in VBA,

Now, we can perform the Autofill method. Here, top row with formulas is wsData.Range(“f2:h2”). And full range that we want to apply formulas to is wsData.Range(“f2:h” & lrowData).

wsData.Range("f2:h2").AutoFill wsData.Range("f2:h" & lrowData)

And the macro is done.

To stop the screen from fluttering, add the below lines to the top and bottom lines of the code.

Application.ScreenUpdating = False
Application.ScreenUpdating = True

Add a message box to alert the user of completion of the macro run.

MsgBox "Import complete"

Create a button to call the macro from the Console sheet.

Go to Developer Tab -> Insert -> Form Controls. Select the first button looking icon. Bring the cursor down to the sheet and size the button by clicking down and dragging to the right. Assign it the macro we’ve just built. And edit the text (right click) on the button to Import Data.

Macro 2: Create Report Copy

Full uninterrupted code is at the bottom.

The objective of this macro is to copy all the data from our Analysis and Data sheets into a new excel file and save it with the file name as give in cell G3. Both sheets contain formulas linking to other sheets that we are not copying over. So, we will need to make sure that we don’t copy over the formulas.

Create a new module. Rename it m02CreateCopy.

Create a new sub.

Sub CreateCopyOfReport()
End Sub

Add the screen updating lines.

Application.DisplayAlerts = False
Application.DisplayAlerts = True

Declare and assign OBJECT variables for the worksheets we will be using.

Dim wsData As Worksheet, wsAnalysis As Worksheet, wsCons As Worksheet
Set wsData = ThisWorkbook.Sheets("Data")
Set wsAnalysis = ThisWorkbook.Sheets("Analysis")
Set wsCons = ThisWorkbook.Sheets("Console")

Declare and assign VALUE variables for the values in cell G1 and G3.

Dim mainFolder As String, reportFileName As String, fileCopyName As String
mainFolder = wsCons.Range("G1").Value
reportFileName = wsCons.Range("G3").Value

To save report copy, we need the full file path i.e. mainFolder joined to “\” joined to reportFile

fileCopyName = mainFolder & "\" & reportFileName

Code to create a workbook is Workbooks.Add(). Since we need to interact with this workbook, once its open (.e.g. paste data into it), we will assign it an Object variable.

Dim wb  As Workbook
Set wb = Workbooks.Add

Create a new sheet called Data after sheet 1 in this new file.

wb.Sheets.Add(after:=wb.Sheets("Sheet1")).Name = "Data"

Copy data from our original Data sheet and paste it here.

wsData.Range("a1").CurrentRegion.Copy

We won’t copy over Formulas. Hence, Paste Special method is needed.

With wb.Sheets("Data")
    .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    .Range("A1").PasteSpecial xlPasteFormats
End With

The With statement is used when addressing multiple properties or methods for the same object.

Create a new sheet: Analysis and copy over data from the report template file.

wb.Sheets.Add(after:=wb.Sheets("Sheet1")).Name = "Analysis"

Since data in sheet: Analysis in the Template file is not in a continuous range, we want use CurrentRegion. Instead, provide the fixed range to copy over.

wsAnalysis.Range("B2:P11").Copy

Paste data from the original Analysis tab over to this sheet.

With wb.Sheets("Analysis")
    .Range("b2").PasteSpecial xlPasteValuesAndNumberFormats
    .Range("B2").PasteSpecial xlPasteFormats
End With

Delete off the Sheet 1.

wb.Sheets("sheet1").Delete

Save this workbook with the report copy name that we have created by joining cells G1 and G3. (xlWorkbookDefault this points to the .xlsx format.)

wb.SaveAs fileCopyName, FileFormat:=xlWorkbookDefault
Close the workbook.
wb.Close savechanges:=False

If you run this macro as-is, you will see messages from Excel asking for confirmation to delete Sheet 1 and also, to save the file. To turn off these messages, apply the below code to the start and end of the macro.

Application.DisplayAlerts = False
Application.DisplayAlerts = True

Add a message that the copy was created.

MsgBox "Copy Created"

Create a button to call the macro from the Console sheet.

Macro 3: Send Email

Full uninterrupted code is at the bottom.

Check here for Notes of How to Send an Outlook Email using VBA.

The objective of this macro is to email a copy our saved report file to our mailing list which can be found in the EmailList worksheet.

Create a new module. Rename it to m03SendEmail.

Create a new sub

Sub SendEmail()
End Sub

Add the Screen Updating lines.

Application.ScreenUpdating = False
Application.ScreenUpdating = True

Declare and assign OBJECT variables for the worksheets we will be using.

Dim wsCons As Worksheet, wsEm As Worksheet, wsAnalysis As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsEm = ThisWorkbook.Sheets("EmailList")
Set wsAnalysis = ThisWorkbook.Sheets("Analysis")

Next, we will grab the emails IDs and put them in together in a string separated by a semi-colon (;). We will need to Loop over the list of Email Ids. To Loop over data, we need to know the starting row and ending row.

Determine the last row by counting the number of rows in the Current Region of the EmailList sheet.

Dim lrowEm As Long
lrowEm = wsEm.Range("A1").CurrentRegion.Rows.Count

Declare and assign a Value variable to hold the string of email ids.

Dim sTo As String

Declare and assign a Value variable to act as the counter for the loop. This will helps us move from the previous row to the next row.

Create the For Loop

For i = 2 To lrowEm
Next i

In each iteration of the For loop, the sTo variable will hold the sTo value from the previous iteration joined to current value in the cell joined to a “;”.

For i = 2 To lrowEm
    sTo = sTo & wsEm.Range("A" & i).Value & ";"
Next i

The last value of sTo will have an extra “;” at the end. We need to remove it.

sTo = Left(sTo, Len(sTo) - 1)

Declare and assign VALUE variables for the values in cell G1 and G3.

Dim mainFolder As String, reportFileName As String, fileCopyName As String
mainFolder = wsCons.Range("G1").Value
reportFileName = wsCons.Range("G3").Value
fileCopyName = mainFolder & "\" & reportFileName

Declare and assign VALUE variable to hold current month name from C2 in Analysis sheet.

Dim reportMonth As String
reportMonth = wsAnalysis.Range("C2").Value

Next, we will create and populate the email. This is a 3 step process.

First, add a reference to the Outlook Library in VBA. Tools -> References -> Microsoft Outlook Object Library.

Second, declare and assign Object variable for the Outlook application, and using it create the Outlook Email.

Dim oApp As Outlook.Application 
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem 
Set oMail = oApp.CreateItem(olMailItem)

Third, we will fill out the properties and methods of this Email object.

Before that, assign a variable to hold the Body and Subject of the Email template.

Dim sBody As String, sSubject As String
sSubject = "Month End Sales Report"

The body will be in HTML format. To break to a new line in the email body, use the <br> tag. We will, also insert the reportmonth variable in the email body.

sBody = "Hi All, <br>" & _
        "Please find attached the month end report for " & reportMonth & ".<br>" & _
        "Regards,"

Fill out the properties and methods of the email Object using the With statement.

With oMail 
End With

The first line is our email To. We can replace it with our variable. sTo.

.To = sTo

We will leave CC and BCC blank.

.CC = "" 
.BCC = "" 

Replace subject with our subject variable.

.Subject = sSubject

Replace body with our body variable.

.HTMLBody = sBody

Add our attachment name in front of the Attachments.Add method.

.Attachments.Add fileCopyName

Bring up the email template.

.Display 

Create a button to call the macro from the Console sheet.

Macro 4: Error Handling

Full uninterrupted code is at the bottom.

Welcome to the bonus section.

Lets recap what we have done so far.

We have an existing macro file which imports data from a source file, places it into our Data worksheet, saves a copy this template file and emails it to users.

The user of this macro is expected to input the main folder path in cell G1 in ur Console worksheet. This will host the source file, our current macro file and the report copy we are about to save. The name of the source file is in G2. And the name of the copy file or the destination file is in G3. Let’s have a look at our data worksheet. Our source data will come into columns A to E. Columns F onwards have formulas that are necessary for the rest of the report. These are not to be altered. Finally, let’s look at the Header worksheet. This contains the Header list as seen in Columns A to E in the Data worksheet.

We have checked if data at any point in the main macro is valid or not. This is what we are about to now.

We will perform the following checks.

Go to our Console worksheet. We will add in a new field here in G4. Call it Data Column Count. The value for this is 5 which is the count of columns from A to E in the Data worksheet.

Create a new worksheet HeaderCheck. Paste (transpose) the column headers from the Data sheet (only columns A to E) into cell A1 of HeaderCheck sheet.

Create a new module. Rename it to m04ErrorHandling.

Create a new sub

Sub ErrorHandling()
End Sub

Add the Screen Updating lines.

Application.ScreenUpdating = False
Application.ScreenUpdating = True

Declare and assign OBJECT variables for the worksheets we will be using.

Dim wsCons As Worksheet, wsData As Worksheet, wsHeader As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsData = ThisWorkbook.Sheets("Data")
Set wsHeader = ThisWorkbook.Sheets("HeaderCheck")

Declare and assign VALUE variables for the values in cell G1, G2, G3 and G4.

Dim mainFolder As String, importFile As String, reportFileName As String, dataColCount As Long
mainFolder = wsCons.Range("G1").Value
importFile = wsCons.Range("G2").Value
reportFileName = wsCons.Range("G3").Value
dataColCount = wsCons.Range("G4").Value

We will first check whether each field in Col G has a value in it or not. We will use an IF statement for this. To check if any one of the value is blank, use the OR operator in the If statement.

If even one cell is blank, we will alert the user, turn on Screen Updating and Exit the sub.

If mainFolder = "" Or importFile = "" Or dataColCount = 0 Or reportFileName = "" Or dataColCount = 0 Then
    MsgBox "All values in Col G in Console sheet must be entered."
    Application.ScreenUpdating = True
    Exit Sub
End If

Declare and assign a VALUE variable to hold the full file path of the source file.

Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

We will check if this file exists using the DIR function. If it doesn’t, the function will return a blank value.

If Dir(fullSourceFileName) = "" Then
    MsgBox "Import file doesn't exist. Please check."
    Application.ScreenUpdating = True
    Exit Sub
End If

We will check if the right five characters in cell G3 has the correct file extension that we are expecting i.e. .xlsx.

If Right(reportFileName, 5) <> ".xlsx" Then
    MsgBox "Extension of report copy file must be .xlsx"
    Application.ScreenUpdating = True
    Exit Sub
End If

For the next checks, we need to open the source file.

Declare and assign an Object variable for the source file we are about to open.

Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

Declare and assign an Object variable to hold the Range of data in Sheet 1.

Dim rngSource As Range
Set rngSource = wb.Sheets("Sheet1").Range("A1").CurrentRegion

We will check if there is no data. If there is no data or just the Header row, then our IF statement will get triggered. This time in addition to alerting the user, we need to close the source file before Exiting the sub.

If rngSource.Rows.Count <= 1 Then
    MsgBox "No data in source file"
    wb.Close savechanges:=False
    Application.ScreenUpdating = True
    Exit Sub
End If

We will check if the count of data columns in the source file equals what we are expecting. We are expecting 5 columns, as given in cell G4.

If rngSource.Columns.Count <> dataColCount Then
    MsgBox "Number of columns in source file is not equal to " & dataColCount
    wb.Close savechanges:=False
    Application.ScreenUpdating = True
    Exit Sub
End If

We will loop over each column header in sheet: HeaderCheck and check if we can Find it in the source file.

To loop over the data, we need to find the last row.

Dim lrowReportHeader As Long
lrowReportHeader = wsHeader.Range("A1").CurrentRegion.Rows.Count

We will check whether each of the Header names exists or not in the Header row of the Source data file. Let’s assign this Header row to a Range object variable.

Dim rngHeader As Range
Set rngHeader = rngSource.Rows(1)

Declare and assign a Value variable to hold the Header name that we need to check.

Dim reportColName As String

The Find() function that we are about to use, will return a Range object. Declare and assign an Object variable to hold the Range.

Declare and assign a counter variable to iterate over the For loop.

Dim i As Long

Begin the For loop.

For i = 1 To lrowReportHeader
Next i

Grab the first Header from Col A of sheet: HeaderCheck

reportColName = wsHeader.Range("A" & i).Value

Use the Find() function to search for this Column Name in the rngHeader Object variable (which is the first row of source file data).

Set rngFind = rngHeader.Find(reportColName, lookAt:=xlWhole)

If a match is not found, the rngFInd variable will remain empty or it will be Nothing.

    If rngFind Is Nothing Then
        MsgBox "Mismatch in column headers"
        wb.Close savechanges:=False
        Application.ScreenUpdating = True
        Exit Sub
    End If

Close the source file.

wb.Close savechanges:=False

Alert the user that the macro has run.

MsgBox "No errors found. OK to continue."

Macro is done. Go to the Console sheet and create a button to call this macro.

Macro 5: Combine all macros

Full uninterrupted code is at the bottom.

This final macro will combine all the four macros that we have built till now. Building a macro that calls other macro is rather simple. But, as we will soon see, it will be necessary to adjust some of the macros to suit end-to-end automation.

First up, lets add a new worksheet called Errors. Here, we are going to print out our error message instead of displaying it in a message.

Now, lets go to the VB editor. Alt F11

Create a new module Call it 00Main. Lets create a new sub.

Sub FullProc()
End Sub

This new sub will trigger or call each of the previous macros that we have built. The code to call a macro is Call and the macro name.

First, we will call our three initial macros.

Call ImportData
Call CreateCopyOfReport
Call SendEmail

We can run it, but the message boxes in the first two macros will get displayed. What we can do, is go into each macro and comment out the message boxes i.e. add an apostrophe in front of the Msgbox code, so that VBA knows to ignore it.

For the error handler macro, we will remove the Msgbox and print the string value in cell A1 in the new sheet Error that we just created.

Declare and assign an OBJECT variable for the Error worksheet.

Dim wsError As Worksheet
Set wsError = ThisWorkbook.Sheets("Error")

Now, replace the code for each Msgbox with wsError.Range(“A1”).Value.

e.g.

MsgBox "All values in Col G in Console sheet must be entered."

Will become

wsError.Range("A1").Value = "All values in Col G in Console sheet must be entered."

Comment out the final confirmation message box in the Error macro.

Come back to the Full Proc sub.

Let’s call the error handling sub, before the previous 3 subs.

Call ErrorHandling

But, we need to code in some logic here. If an error is detected by the error handling sub then we need to alert the user and not run the remaining macros.

So, how do we detect that an error has occurred. We will check whether the value in cell A1 in the Error worksheet is blank or not after the first macro is run. If its blank, we will continue with the rest of the macro. If its not blank and an error is detected, we will alert the user and exit out of this sub.

Lets code this into our main sub.

Declare and assign an OBJECT variable for the Error worksheet.

Dim wsError As Worksheet
Set wsError = ThisWorkbook.Sheets("Error")

Next, clear out the value from cell A1 in the worksheet, before the Error Handling macro runs. This is to clear out any errors populated from a previous run of the macro.

wsError.Range("A1").Value = ""

After the error handling macro runs, we will use an IF statement to check if the value in cell A1 is blank or not. If it’s not, we will display a message saying that an error occurred.

If wsError.Range("A1").Value <> "" Then
    MsgBox "One error has occured. Check the error worksheet."
    wsError.Activate
    Exit Sub
End If

Macro is done. Go to the Console sheet and create a button to call this macro. Remove previous macro buttons.

Following Sections have the raw, uninterrupted code.

Full Code: Macro 1 (Import File)

Option Explicit

Sub ImportData()

Application.ScreenUpdating = False

Dim wsCons As Worksheet, wsData As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsData = ThisWorkbook.Sheets("Data")

Dim mainFolder As String, importFile As String
mainFolder = wsCons.Range("G1").Value
importFile = wsCons.Range("G2").Value

Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

wsData.Columns("A:E").ClearContents

Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

Dim rngToCopy As Range
Set rngToCopy = wb.Sheets("Sheet1").Range("A1").CurrentRegion

rngToCopy.Copy wsData.Range("A1")

wb.Close savechanges:=False

Dim lrowData As Long
lrowData = wsData.Range("A1").CurrentRegion.Rows.Count

wsData.Range("F2:H2").AutoFill wsData.Range("F2:H" & lrowData)

MsgBox "Import Complete"

Application.ScreenUpdating = True
End Sub

Full Code: Macro 2 (Create Copy)

Option Explicit

Sub CreateCopyOfReport()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim wsCons As Worksheet, wsData As Worksheet, wsAnalysis As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsData = ThisWorkbook.Sheets("Data")
Set wsAnalysis = ThisWorkbook.Sheets("Analysis")

Dim mainFolder As String, reportFileName As String, fileCopyName As String
mainFolder = wsCons.Range("G1").Value
reportFileName = wsCons.Range("G3").Value
fileCopyName = mainFolder & "\" & reportFileName

Dim wb As Workbook
Set wb = Workbooks.Add

wb.Sheets.Add(after:=wb.Sheets("sheet1")).Name = "Data"

wsData.Range("A1").CurrentRegion.Copy

With wb.Sheets("Data")
    .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    .Range("A1").PasteSpecial xlPasteFormats
End With

wb.Sheets.Add(after:=wb.Sheets("sheet1")).Name = "Analysis"

wsAnalysis.Range("B2:P11").Copy

With wb.Sheets("Analysis")
    .Range("B2").PasteSpecial xlPasteValuesAndNumberFormats
    .Range("B2").PasteSpecial xlPasteFormats
End With

wb.Sheets("Sheet1").Delete

wb.SaveAs fileCopyName, FileFormat:=xlWorkbookDefault
wb.Close savechanges:=False

MsgBox "Copy Created"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Full Code: Macro 3 (Send Email)

For this code to work, make sure you have set reference to the Outlook Object Library. Tools -> References -> Microsoft Outlook Object Library.

Option Explicit

Sub SendEmail()
Application.ScreenUpdating = False

Dim wsCons As Worksheet, wsEm As Worksheet, wsAnalysis As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsEm = ThisWorkbook.Sheets("EmailList")
Set wsAnalysis = ThisWorkbook.Sheets("Analysis")

Dim lrowEm As Long
lrowEm = wsEm.Range("A1").CurrentRegion.Rows.Count

Dim sTo As String
Dim i As Long

For i = 2 To lrowEm
    sTo = sTo & wsEm.Range("A" & i).Value & ";"
    
Next i

sTo = Left(sTo, Len(sTo) - 1)

Dim mainFolder As String, reportFileName As String, fileCopyName As String
mainFolder = wsCons.Range("G1").Value
reportFileName = wsCons.Range("G3").Value
fileCopyName = mainFolder & "\" & reportFileName

Dim reportMonth As String
reportMonth = wsAnalysis.Range("C2").Value

' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application

' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim sBody As String, sSubject As String
sSubject = "Month End Sales Report"

sBody = "Hi All,<br>" & _
        "Please find attached the month end report for " & reportMonth & ".<br>" & _
        "Regards,"

With oMail
    .To = sTo
    .CC = ""
    .BCC = ""
    .Subject = sSubject
    .HTMLBody = sBody
    .Attachments.Add fileCopyName
    .Display
End With


Application.ScreenUpdating = True
End Sub

Full Code: Macro 4 (Error Handling)

Option Explicit

Sub ErrorHandling()
Application.ScreenUpdating = False

'Declare worksheets
Dim wsCons As Worksheet, wsData As Worksheet, wsHeader As Worksheet
Set wsCons = ThisWorkbook.Sheets("Console")
Set wsData = ThisWorkbook.Sheets("Data")
Set wsHeader = ThisWorkbook.Sheets("HeaderCheck")

'Declare variables
Dim mainFolder As String, importFile As String, reportFileName As String
Dim dataColCount As Long
mainFolder = wsCons.Range("G1").Value
importFile = wsCons.Range("G2").Value
reportFileName = wsCons.Range("G3").Value
dataColCount = wsCons.Range("G4").Value

'Check 1: Col G cant be blank
If mainFolder = "" Or importFile = "" Or reportFileName = "" Or dataColCount = 0 Then
    MsgBox "All values in Col G in Console sheet must be entered."
    Application.ScreenUpdating = True
    Exit Sub
End If

'Check2: Does source file exist
Dim fullSourceFileName As String
fullSourceFileName = mainFolder & "\" & importFile

If Dir(fullSourceFileName) = "" Then
    MsgBox "Import file doesn't exist. Please check."
    Application.ScreenUpdating = True
    Exit Sub
End If

'Check3: Extension of report copy file must be .xlsx
If Right(reportFileName, 5) <> ".xlsx" Then
    MsgBox "Extension of report copy file must be .xlsx"
    Application.ScreenUpdating = True
    Exit Sub
End If

Dim wb As Workbook
Set wb = Workbooks.Open(fullSourceFileName)

Dim rngSource As Range
Set rngSource = wb.Sheets("Sheet1").Range("A1").CurrentRegion

'Check4: Source Data Empty
If rngSource.Rows.Count <= 1 Then
    MsgBox "No data in source file"
    wb.Close savechanges:=False
    Application.ScreenUpdating = True
    Exit Sub
End If

'Check5: Column Count Mismatch
If rngSource.Columns.Count <> dataColCount Then
    MsgBox "Number of columns in source file is not equal to " & dataColCount
    wb.Close savechanges:=False
    Application.ScreenUpdating = True
    Exit Sub
End If

'Check6: Column Headers Match
Dim lrowReportHeader As Long
lrowReportHeader = wsHeader.Range("A1").CurrentRegion.Rows.Count

Dim rngHeader As Range
Set rngHeader = rngSource.Rows(1)

Dim i As Long
Dim reportColName As String
Dim rngFind As Range

For i = 1 To lrowReportHeader
    reportColName = wsHeader.Range("A" & i).Value
    Set rngFind = rngHeader.Find(reportColName, lookAt:=xlWhole)
    If rngFind Is Nothing Then
        MsgBox "Mismatch in column headers"
        wb.Close savechanges:=False
        Application.ScreenUpdating = True
        Exit Sub
    End If
Next i


wb.Close savechanges:=False
MsgBox "No errors found. OK to continue."

Application.ScreenUpdating = True
End Sub

Full Code: Macro 5 (Combined)

Please follow the settings as indicated in the video or the instructions above. Summary of changes: Remove end confirmation Msgbox from initial macros. Replace individual error Msgbox with wsError.Range(“A1”).Value

Option Explicit

Sub FullProc()

Dim wsError As Worksheet
Set wsError = ThisWorkbook.Sheets("Errors")

wsError.Range("A1").Value = ""
Call ErrorHandling

If wsError.Range("A1").Value <> "" Then
     MsgBox "One error has occured. Check the error worksheet."
     wsError.Activate
     Exit Sub
End If

Call ImportData
Call CreateCopyOfReport
Call SendEmail

End Sub