Five handy Excel macros to take boring chores out of your workday

5 min readMay 2, 2020

Inserting multiple sheets or copying values to make mass changes? Let’s automate them with Visual Basic for Applications (VBA).

Admit it or not, we all happen to do boring tasks manually in our daily routines. Most of the time, they are repetitive and should be done in a certain pattern. There is an ocean of tools and programming languages such as Python, Alteryx, R, etc., to automate such tasks, but it is always better to carry on with the one on hand.

Yes, you guessed it right. I am talking about Visual Basic for Application (VBA) for any Microsoft Office product. Recently, I have had to insert a column to a particular sheet within more than 300 spreadsheets and it has given me the opportunity to delve into the fun VBA world of MS Excel.

Before I share these useful macros, here is a great comprehensive article about their usage and the mindset we should adapt. Also, let’s learn how to add “Developer Tab” on the ribbon in here in a concise way to do it.

Now that we have the Developer Tab ready, we can open the Visual Basic editor (Alt + F11 if you like shortcuts), add a module on the upper-left hand corner of the screen and start writing our very first macro.

How to (Un)protect all worksheets at once in all files in the folder

Protecting the excel files in common folders used by many colleagues to avoid any unintended changes can be a great idea. In our case, we are assuming that we would like to set a password for each worksheet in each file.

Below I am giving the main part of the code with the comments. For the next tasks, we will be only changing the code that we need.

Sub Folder_Loop()
    
'Excel VBA code to make mass changes on files in a folder, defines the objects
    
    Dim MyFolder As String, MyFile As String'Opens a file dialog box for the user to select a folderWith Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       MyFolder = .SelectedItems(1)
       Err.Clear
    End With'stops screen updating, calculations, events, and status bar updates to help code run fasterApplication.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual'loops through and opens each file in the folder you selected
 'and then close it by saving the changes before opening the next fileMyFile = Dir(MyFolder & "\", vbReadOnly)
    
    Do While MyFile <> ""
        DoEvents
        On Error GoTo 0
        Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
        
'automates the tasks, it is (Un)protect all worksheets at once in our case
'replace XyzPass98 with the password you want and
'simply use Unprotect method instead of Protect to unlock the sheets you protected        Dim ws As Worksheet
Dim password As String
password = "XyzPass98"
For Each ws In Worksheets
ws.Protect password:=password
Next ws'Here ends the process, and two lines below closing the files and saveWorkbooks(MyFile).Close SaveChanges:=True
        MyFile = Dir
    Loop'turns settings back on that you turned off before looping filesApplication.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationManual'Message box you see when it is over. Feel free to change the message in the quotation marks you'd like to seeMsgBox "Over, thanks"
0End Sub

How to convert all formulas into values

We all work on tables that have SUM, SUMIF, worksheets that are merged with some other data from other excel sources with the help of VLOOKUPs. When these formulas are not converted into values, it might increase the file size as well as the time for the file it takes to open.

Here is our case:

In the common folder we keep our sales data for three years. The daily sales report has 5 different worksheets and we are asked to convert all formulas into values in our daily sales reports.

Dim ws As Worksheet
For Each ws In Worksheets
With ws.UsedRange
.Value = .Value
End With
Next ws

How to sort worksheets alphabetically

One might argue that alphabetical sorting of worksheets doesn’t bring any added value and I’d partially agree on that. However, sorting worksheets can help us to avoid mistakes if we happen to paste values based on sheet index number. Another cool thing about this code is that it actually shows us how we state something that is not numeric in VBA.

Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

How to insert multiple worksheet into different files

‘This will open each file and asks you to enter
MsgBox "Opening File->" & Application.ActiveWorkbook.nameDim i As Integer
i = InputBox("Enter number of sheets to insert.", "Enter Multiple Sheets")
Sheets.Add After:=ActiveSheet, Count:=i

How to copy values from worksheets

Sheets("Sheet1").Select
Range("A1:D7").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1:D7").Select
ActiveSheet.Paste

We copy-pasted just six lines of code and got what we want. Easy, right? Well, this is not actually what it looks like. This requires a selection and it is a waste of processor time. It might not create problems while dealing with small data, but working with large datasets might take a lot of time.

Here is a better way to write this code:

Sheets(“Sheet2”).Range("A1:D7").Value = Sheets(“Sheet1”).Range("A1:D7").Value

Or even better by using the index number of the sheets.

Sheets(2).Range("A1:D7").Value = Sheets(1).Range("A1:D7").Value

What if they are not in order and I might be copying the wrong values? Well, remember to sort them alphabetically before using this part.

Let’s finish the process with a bit more complex explanations. This can be tailored for different tasks and might come useful to anyone who is willing to take boring chores out of their workday.

'Select the worksheet named SummarySheets("Summary").Select'Insert a column on B by shifting the other columns to right       Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Name the column as Date by defining the value on cell BRange("B1").Value = "Date"'If there is a filter on the activesheet, deactivate it.
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False'Starting from B2, add the value in the active workbook's name starting from the 7th character and moving right by 10 character and keep adding that value as the number of rows that exist in the sheet.With Sheets("Summary")
 Set RngCol = .Range("B2", .Cells(Rows.Count, 1).End(xlUp))
 End With
 LastRow = RngCol.Rows.Count + 1
 Range("B2:B" & LastRow).Value = Mid(ActiveWorkbook.Name, 7, 10)

References:
https://trumpexcel.com/excel-macro-examples/
https://www.launchexcel.com/most-common-excel-vba-commands/#3-Cells.Find-function-to-search-for-values

Five handy Excel macros to take boring chores out of your workday

Written by BPiskin