Can I make this macro more efficient or faster?

Question

I am brand new to coding. This macro runs slow and I'm hopeful that someone can help me clean it up. Thanks in advance for your help.

I developed the code to update my company's "Call Router" worksheet with new leads purchased from an outside source. The leads come to us in raw format in a worksheet called Fresh Agents Leads. Once the "Fresh Agent Leads" sheet is copied into the "MSS Call Routing Master List" file which houses the "Call Router" Worksheet, the macro reduces the raw data so that the parts we do not use are eliminated. It then re-formats what remains to match the formatting of the Old Call Router Worksheet and merges the two. It then renames the new Master sheet to Call Router.

The code is designed to start inside the workbook that houses the Fresh Agent Leads Sheet. The user is instructed to have both the Fresh Agents Leads File and the MSS Call Routing Master List opened on the desktop before executing the code.

    Sheets("Fresh Agent Leads").Select
    Sheets("Fresh Agent Leads").Copy After:=Workbooks( _
        "MSS Call Routing Master List.xlsx").Sheets(1)
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    Selection.Copy
    Columns("F:F").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("G:S").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RIGHT(RC[1],4))"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C1048575")
    Range("C1:C1048575").Select

    Sheets("Call Router").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("Fresh Agent Leads").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.Run "PERSONAL.xlsb!MergeIdenticalWorksheets"
    Columns("C:C").Select
    Selection.NumberFormat = "0000"
    Range("A:A,B:B,F:F").Select
    Range("F1").Activate
    Selection.ColumnWidth = 14
    Columns("E:E").Select
    Selection.ColumnWidth = 25
    Columns("C:C").Select
    Selection.ColumnWidth = 8.29
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.RowHeight = 30
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = True
    Range("E2").Select
    ActiveWindow.FreezePanes = True
    Sheets(Array("Call Router", "Fresh Agent Leads")).Select
    Sheets("Call Router").Activate
    ActiveWindow.SelectedSheets.Delete
    Sheets("Master").Select
    Sheets("Master").Name = "Call Router"
    Range("C23").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Save
End Sub

Answer 1

A few tips:

1. Avoid .Select and .Activate as much as possible. Recording macros is a good start to VBA, but the first big step is leaving the "comfort zone" provided by these properties. In the beginning, they're good, but they're bound to create problems in the long run.

2. Read up on the following "basic" procedures: copying/pasting/inserting ranges, creating/deleting sheets, and determining the last rows/columns of a sheet or range that has relevant data. These three are your bestfriends. By learning these three by heart, you can manipulate a lot in Excel VBA.

3. After (2), start learning how to dimension variables and/or objects. Setting the jargon aside, this is basically just akin to giving each important thing you're working on "nicknames". Say you're working on 3 sheets. You don't want to keep on referring to ThisWorkbook.Sheets("Sheet1") and so on and on. You'd much rather want Sh1 or Sh2 instead.

4. Learn how to bunch similar procedures together using Union, With, or the like. This goes hand in hand with (1) above. You'll see an example later on this.

5. Application.ScreenUpdating - one of the best time-shaving tricks in Excel VBA.

Now, a few samples:

(1) Avoiding .Select || Learning to use the very nice .Copy one-liner

This part...

Range("A1").Select
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste

...can be reduced to:

Range("A1").Copy Range("F:F")

From four lines to just one. And it's much more readable. The second code snippet aboves basically reads, "Copy A1's value to the whole F column". Note that that's actually quite memory intensive, as in Excel 2010, you're actually pasting to a million and more rows with that command. Better be specific, like Range("F1:F1000").

(2) Bunching commands together

Bunching commands together in "written" VBA is different from the way you do it in macros. Since macros are recorded, everything is based on real time modifications. In "written" VBA, you can specify an action that will allow you to apply a single action on multiple objects. Let's say for example you want to delete Columns A and C while shifting all relevant data to the left.

When recording a macro to do this, you can select both A and C and delete them at the same time. However, most beginners take the safe path and record the deletion of columns one at a time which--while safe--is highly counterintuitive. Selecting both before deleting is the best option.

In written VBA, the second method above is a massive no-no (or at least, it's not the norm). Unless there's a specific and necessary reason, bunching similar commands together is the convention as it both eliminates error to a large degree and is not resource intensive.

In your code...

Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("G:S").Select
Selection.Delete Shift:=xlToLeft

... is such a pain to read. We don't know why there are two deletions there, we don't know for sure where the data in Column S originally was, etc, etc. In instances like this, determining ahead of time the ranges you want to delete and executing the deletion is the perfect way.

Let's assume for example's sake that you want to delete the columns A, C, E, and F to O. A neat approach like follows will pull this off quite quickly and efficiently.

Union(Range("A:A"),Range("C:C"),Range("E:E"),Range("F:O")).Delete

Union is one of your early bestfriends. As with set notation in math, ranges you specify are put together into a set of ranges together and are actioned upon at the same time (in this case, .Deleted at the same time). Since the default shift is to the left, we can altogether remove the Shift:=xlToLeft line (another nifty VBA fact).

(3) With - one thing you cannot live without

At this point, you might be thinking, what about multiple actions on these ranges? We've only done single actions on multiple ranges and not the other way around. This is the point where With comes in. In this context, With will be used only on Ranges but it can be used on almost anything in VBA. Objects, ranges, external applications, etc. I will not delve on this, but suffice to say that using With is like using an anchor on something you want to work on with a few procedures.

In your code, we find...

Columns("C:C").Select
Selection.ColumnWidth = 8.29
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Rows("1:1").Select
Selection.RowHeight = 30
With Selection
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

... which can be reduced to:

With Columns("C:C")
    .ColumnWidth = 8.29
    .HorizontalAlignment = xlCenter
End With
With Rows(1:1)
    .RowHeight = 30
    .WrapText = True
End With

Basically, we did two things here. First, we anchored on Column C, and did two actions on it: set the column width, then the horizontal alignment. After anchoring to Column C and modifying it, we change the anchor to the whole of row 1 and we modify its height and set it to wrap text to cell width. From 24 lines, we've reduced that macro block to just 8 lines. How's that for brevity? :)

Why did I make without the other lines? As in the previous example (Union), we can make do with some lines that are either the default value anyway or are not modified. There will be exceptions to these, but they will be far and few and are a bit off your level for now. You'll get there.

(4) Creating/modifying sheets and avoiding .Activate, and a touch on dimensions

One of the pitfalls of beginners in VBA is that they use ActiveWorkbook, ActiveSheet, and .Activate a lot. This is not bad per se, but it's not good either. It's convenient to use, but it will cause a myriad of headaches if you incorporate it in really complex subroutines and functions.

To combat this, we first go into the idea of dimensioning or qualifying your objects. This is done by first declaring a keyword and then a data type. I will not delve into this further, as there are lots of VBA tutorials you can read for this, so I'll just point out some important ones.

Let's say you're working on two open workbooks. We can create a "nickname" for each of them so you can refer to them without having to type whole lines of reference.

Dim SourceWbk As Workbook
Dim TargetWbk As Workbook

The two lines above read as, "SourceWbk/TargetWbk is my nickname and I am dimensioned as a workbook, so I'll be expecting to be referred to a workbook". Now that we've created dimensions for them, we can point them to what they will stand for.

Set SourceWbk = ThisWorkbook
Set TargetWbk = Workbooks("I AM THE MASTER REPORT")

Note the "=" here. Now, we've basically declared that from now on, SourceWbk is going to refer to the workbook containing this code, and TargetWbk is going to refer to the open workbook named "I AM THE MASTER REPORT". Now let's look at the simple act of copying a sheet from SourceWbk to TargetWbk.

SourceWbk.Sheets("Sheet1").Copy After:=TargetWbk.Sheets("Sheet1")

Looks familiar? That's because this is pretty much the same as this recorded block of your code:

Sheets("Fresh Agent Leads").Select
Sheets("Fresh Agent Leads").Copy After:=Workbooks( _
    "MSS Call Routing Master List.xlsx").Sheets(1)

Now, you can go one step further and name the sheets themselves, then copy them. Example follows:

Dim FAL As Worksheet 'Expects a worksheet.
Dim LastSheet As Worksheet

Set FAL = SourceWbk.Sheets("Fresh Agent Leads")
Set LastSheet = TargetWbk.Sheets("Sheet1") 'You can use a number index or specific name

FAL.Copy After:=LastSheet

At this point the code is very, very short and sweet already. No hassles, and the only effort you actually need is to remember what the "nicknames" refer to. Take note that there are specific words you should NOT be using as variable names. As much as possible, make it personalized but reasonable. Simply naming a sheet as Sh is good but it gets you nowhere in a file with 100 sheets each with different purposes.

(5) The Application Trickbook

In Excel VBA, there are a few things you can manipulate to increase the efficiency of your code. After all is said and done, a macro is just a repeated action. Running a recorded or a written one both will take you through the actions. .Select will select specific ranges and you'll see them get selected. .Activate will do the same, more or less. .Copy will show you those "ants" and the highlights they leave behind. All these contribute to a longer and often sloppy visual execution of the code. Herein steps the ScreenUpdating "trick".

Mind you, it's not really a trick. Most people consider them highly important parts of their code, but their inclusion into "layman" VBA modules are nonetheless helpful. One of the best practices is setting Application.ScreenUpdating = False at the beginning of a subroutine then setting it back to True at the end.

ScreenUpdating will "freeze" your screen, making everything happen without you seeing them. You won't see items getting copied or ranges getting selected. You won't see closed workbooks being opened and closed. While this only affects Excel when you call it, it's nonetheless invaluable.

A quick and dirty list (do not use this as an absolute reference!) of Application tricks:

• .ScreenUpdating (False/True): Eliminates the visual updating of Excel when False. Absolutely necessary when copy-pasting or deleting rows.
• .Calculation (xlCalculationAutomatic/xlCalculationSemiautomatic/xlCalculationManual): Similar to the Formulas > Calculation Options ribbon function, setting this to Manual will suspend all calculations. Highly recommended especially when you're updating ranges that are depended on by loads of VLOOKUP or INDEX formulas.
• .EnableEvents(False/True): Disables triggering event based procedures. A bit advanced, but suffice to say that if you've got some automatic macro triggering on event-based changes, this will suspend them in favor of the current running macro.

There are loads of others and it will be to your best interest to learn most of them. ;)

THE BIG FINALE

Here's a sample code taken from your recorded macro that uses all the above techniques and takes into consideration the procedures you executed on your macro. This is not your whole code. Read this, test this, modify this and you'll improve a lot in a day.

Sub RefinedCode()

    Dim SourceWbk As Workbook, TargetWbk As Workbook
    Dim FALSht As Worksheet, FALSht2 As Worksheet, MasterSht As Worksheet

    Application.ScreenUpdating = False 'We won't see the copy-paste and column deletion happening but they will happen. 

    Set SourceWbk = ThisWorkbook
    Set TargetWbk = Workbooks("MSS Call Routing Master List")
    Set FALSht = SourceWbk.Sheets("Fresh Agent Leads")

    With TargetWbk          
        Set MasterSht = .Sheets("Master") 'Basically reads as Set MasterSht = TargetWbk.Sheets("Master")
        FAL.Copy After:= .Sheets(1)
        Set FALSht2 = .Sheets("Fresh Agent Leads")
    End With

    With FALSht2
        Union(.Range("A:A"),.Range("C:C"),.Range("E:O")).Delete
        With .Rows(1)
            .RowHeight = 30
            .WrapText = True
        End With
        .Range("A1").Copy .Range("F1:F100")
    End With

    MasterSht.Name = "Call Router"
    TargetWbk.Save
    SourceWbk.Close

    Application.ScreenUpdating = True 'Return to default setting.

End Sub

Hope this helps.