Writing an Excel Macro with VBA

I love Excel, and one of my favorite parts of using Excel is the ability to save a ton of time through macros. I have made dozens of macros in the past, and today I will be reviewing a macro I made from scratch in early 2019. (Please note, I’ve modified some data for company privacy.)

What is an Excel macro?

If you have tasks in Microsoft Excel that you repeatedly do, you can record a macro to automate those tasks. A macro is an action or a set of steps that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes.

Office.com

In addition to being able to “record” a macro in Excel, you can also “write” a macro in Visual Basic for Application, or V.B.A.

In this situation, I wrote a macro in VBA and also recorded parts of it too.

The Report (Before)

Here is an example, with faux data, of what this report looks like initially. It’s a list of vendors and their payment details.

This is very overwhelming when this report shows the typical 6,000 + rows!

I wanted to simplify this process to save time each time I complete this report.

The frequency of this report is weekly, to bi-weekly and this macro saves 30 to 50 minutes per report.

The Process (Creating the Macro)

Once I had this report up, I was able to begin building my macro. Here is what my macro did, step by step:

1) Copy the “Original Report” tab so that a new “Updated Report” tab can be modified.
(This allows the person running the report to have the original date to refer to if needed.)

2) Delete 30 columns: the report started with a total of 44 columns and brought it down to 14 columns.

3) Re-sized all columns so the report has a cleaner, more organized look.

4) Created new columns and renamed the headers for each new column.

5) Programmed four functions into the newly created columns to search for specific data.

— The 1st function finds all the rows with active vendors, that get paid by check and were paid in the last 30-days.

— The 2nd function finds all the rows with a “payment due” to the vendor.

— The 3rd function finds all the rows with vendors that have e-mails.

— The 4th function verifies if a vendor is new in our systems.

6) Once the functions are programmed, the macro fills the function in to all of the cells of that one column. It only fills until the last row with data though, not all the way to the bottom of the Excel document.

7) The results of the functions is what we need to save. I programed the macro to then copy all the columns with functions programmed into them, then “pastes with values” to save only the results, and removes​ the function.

8) Lastly, the macro automatically saves the document.

Macro Button

I also took the below steps to create the Macro BUTTON and assigned a macro to the button. 

Then, when I am done with the steps below, I can click the button and the macro will run on the Original tab.

1) Select the Developer tab.

2) Select Button.

3) Click where the button should go.

4) Once the pop-up appears, choose the macro that you want to assign. If you didn’t select a macro when you created the button, then you can right-click the button and select “assign macro”.

The Report (After)

After this macro runs, the document looks like this:

Putting together this macro took me a little over 3 hours. It removed all of the extra columns, and the functions complete most of the research for me. They find the new vendors, vendors with payments due and vendors who are missing their e-mails.

With this macro, I can save that much time in trying to find the data that I need to look at from 6,000 + number of rows. I’m still learning and have a long way to go with Visual Basic for Applications, but writing Excel macros is one of my favorite skills that I’ve developed in the last few years!

 

The Macro

With all of that said, here is the macro that I wrote from scratch with the help of my previous knowledge, google and some recording.

Sub Vendor_Scrub()

‘ Vendor_Scrub Macro

‘Copy the original sheet and rename sheets
Sheets(“Original Report”).Select
Sheets(“Original Report”).Copy Before:=Sheets(1)
Sheets(“Original Report (2)”).Select
Sheets(“Original Report (2)”).Name = “Updated Report”
Sheets(“Updated Report”).Select

‘Delete all extra unneeded columns.
Columns(“AR:AR”).Select
Selection.Delete

Columns(“AQ:AQ”).Select
Selection.Delete

Columns(“AP:AP”).Select
Selection.Delete

Columns(“AO:AO”).Select
Selection.Delete

Columns(“AN:AN”).Select
Selection.Delete

Columns(“AM:AM”).Select
Selection.Delete

Columns(“AL:AL”).Select
Selection.Delete

Columns(“AK:AK”).Select
Selection.Delete

Columns(“AJ:AJ”).Select
Selection.Delete

Columns(“AI:AI”).Select
Selection.Delete

Columns(“AH:AH”).Select
Selection.Delete

Columns(“AE:AE”).Select
Selection.Delete

Columns(“AD:AD”).Select
Selection.Delete

Columns(“AC:AC”).Select
Selection.Delete

Columns(“AB:AB”).Select
Selection.Delete

Columns(“AA:AA”).Select
Selection.Delete

Columns(“Z:Z”).Select
Selection.Delete

Columns(“Y:Y”).Select
Selection.Delete

Columns(“X:X”).Select
Selection.Delete

Columns(“V:V”).Select
Selection.Delete

Columns(“S:S”).Select
Selection.Delete

Columns(“R:R”).Select
Selection.Delete

Columns(“P:P”).Select
Selection.Delete

Columns(“O:O”).Select
Selection.Delete

Columns(“N:N”).Select
Selection.Delete

Columns(“M:M”).Select
Selection.Delete

Columns(“L:L”).Select
Selection.Delete

Columns(“K:K”).Select
Selection.Delete

Columns(“J:J”).Select
Selection.Delete

Columns(“I:I”).Select
Selection.Delete

Columns(“H:H”).Select
Selection.Delete

Columns(“G:G”).Select
Selection.Delete

Columns(“F:F”).Select
Selection.Delete

Columns(“E:E”).Select
Selection.Delete

Columns(“C:C”).Select
Selection.Delete

‘Select and Highligh all of the most important Column Headers
Range(“A1:O1”).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

‘Size all columns.

Cells.Select
Cells.EntireColumn.AutoFit

Range(“B:B,C:C,D:D,F:F,G:G,I:I,J:J”).Select
Selection.ColumnWidth = 18

‘Name new columns.

Range(“K1”).Select
ActiveCell.FormulaR1C1 = “Active, Check, 30-Days?”

Range(“L1”).Select
ActiveCell.FormulaR1C1 = “Balance Due?”

Range(“M1”).Select
ActiveCell.FormulaR1C1 = “Has E-mail?”

Range(“N1”).Select
ActiveCell.FormulaR1C1 = “New Vendor?”

Range(“O1”).Select
ActiveCell.FormulaR1C1 = “Previous Notes”

‘Update formula for Column K – Column O

Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row

Range(“K2:K” & LastRowColumnA).Formula = “=IFS(AND(RC[-10]=””active””,RC[-6]=””check””,RC[-2]>=(TODAY()-30)),TRUE)”

Range(“L2:L” & LastRowColumnA).Formula = “=IF(RC[-4]<>0,””Payments Due””,””No Payments””)”

Range(“M2:M” & LastRowColumnA).Formula = “=IF(OR(RC[-9]<>””””,RC[-7]<>””””),””E-mail Available””, “”No E-mail””)”

Range(“N2:N” & LastRowColumnA).Formula = “=IF(ISNUMBER(SEARCH(“”new vendor””,RC[-12])), “”Yes””, “”No””)”

‘Copy and Paste all formulas.

Columns(“K:O”).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

‘Save

ActiveWorkbook.Save

End Sub

Pin It on Pinterest