What VBA macros are

VBA (Visual Basic for Applications) is the programming language built into all Microsoft Office applications. A macro is a VBA program — a named sequence of instructions that Excel executes when triggered. Macros can automate virtually any action you can perform manually: opening files, copying data, formatting cells, creating sheets, saving outputs, and interacting with other Office applications.

You do not need to be a programmer to use macros. The four scripts in this article are complete and ready to use — you can copy, paste, and run them without modification, or make small adjustments to adapt them to your specific situation.

Enabling the Developer tab

The Developer tab is hidden by default. To enable it: File → Options → Customize Ribbon → check Developer in the right-hand panel → OK. The Developer tab appears in the ribbon with buttons for recording macros, opening the VBA editor, inserting form controls, and managing add-ins.

The VBA editor

Developer → Visual Basic opens the VBA editor. The left panel shows a tree of all open workbooks and their components. Double-click a module to open it. To create a new module: right-click the workbook name → Insert → Module. Type or paste your macro code into the module window.

Quick tip: Press Alt+F11 from anywhere in Excel to toggle the VBA editor open and closed. This is faster than navigating through the Developer tab.

Macro 1: Merge multiple Excel files into one

This macro opens all Excel files in a specified folder and copies their data into the active workbook. Useful for consolidating monthly report files, combining regional data exports, or merging files from multiple contributors into a single dataset.

Sub MergeFiles()
  Dim bookList As Workbook
  Dim mergeObj As Object, dirObj As Object
  Dim filesObj As Object, everyObj As Object
  Application.ScreenUpdating = False
  Set mergeObj = CreateObject("Scripting.FileSystemObject")
  ' ← Change this to your folder path
  Set dirObj = mergeObj.Getfolder("C:\Users\YourName\Desktop\DataFolder")
  Set filesObj = dirObj.Files
  For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    ' Copy from A2 (skip header) to last row
    Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    bookList.Close
  Next
End Sub

To use: change the folder path in the Getfolder line to your actual folder. Run the macro with the destination workbook open and the first sheet active. The macro opens each file, copies all data from row 2 downward (skipping headers), pastes it below the last row of existing data in your workbook, and closes the source file.

Macro 2: Split a sheet by column value into separate tabs

This macro reads the unique values in a specified column and creates a separate worksheet for each value, copying the relevant rows into each new sheet. Useful for splitting a consolidated report into per-region, per-team, or per-product sheets.

Sub SplitByColumn()
  Dim ws As Worksheet
  Dim vcol As Integer
  vcol = 1 ' ← Column to split by (1=A, 2=B etc)
  Set ws = Sheets("Sheet1") ' ← Your sheet name
  Dim lr As Long
  lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
  Dim myarr As Variant
  Dim icol As Long
  icol = ws.Columns.Count
  ws.Cells(1, icol) = "Unique"
  ' Collect unique values from split column
  For i = 2 To lr
    If ws.Cells(i, vcol) <> "" And _
      Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
      ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
    End If
  Next
  myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
  ws.Columns(icol).Clear
  ' Create a sheet for each unique value
  For i = 2 To UBound(myarr)
    ws.Range("A1:C" & lr).AutoFilter field:=vcol, Criteria1:=myarr(i)
    If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
      Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i)
    End If
    ws.Range("A1" & ":" & "A" & lr).EntireRow.Copy Sheets(myarr(i)).Range("A1")
    Sheets(myarr(i)).Columns.AutoFit
  Next
  ws.AutoFilterMode = False
End Sub

Adjust vcol to the column number containing the values to split by. Adjust the sheet name on line 4. The macro creates one new worksheet per unique value and copies the matching rows with headers into each.

Macro 3: Save the active sheet as a PDF

One-click PDF export of the current sheet directly to your Desktop. No dialog boxes, no navigation. The file is named after the sheet tab.

Sub SaveSheetAsPDF()
  Dim shell As Object
  Dim destPath As String
  Set shell = CreateObject("WScript.Shell")
  destPath = shell.SpecialFolders("Desktop") & "\" & ActiveSheet.Name
  ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=destPath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

The PDF saves to your Desktop with the sheet name as the filename and opens immediately after creation. Change OpenAfterPublish to False to suppress the automatic opening. Change "Desktop" to another SpecialFolders path to save elsewhere.

Macro 4: Toggle help annotations on/off

This macro shows and hides a set of named shapes on a sheet — instruction callouts, arrows, and labels that guide users through a complex sheet — with a single button click. When clicked, it checks the button label and either hides all the annotation shapes (switching to "Show Help") or shows them all (switching to "Hide Help").

Sub ToggleHelp()
  With ActiveSheet.Shapes("ToggleButton").TextFrame2.TextRange.Characters
    If .Text = "Hide Help" Then
      .Text = "Show Help"
      ActiveSheet.Shapes("HelpArrow1").Visible = False
      ActiveSheet.Shapes("HelpCallout1").Visible = False
      ' Add all your annotation shape names here
    Else
      .Text = "Hide Help"
      ActiveSheet.Shapes("HelpArrow1").Visible = True
      ActiveSheet.Shapes("HelpCallout1").Visible = True
      ' Add all your annotation shape names here
    End If
  End With
End Sub

Replace shape names with the actual names of your annotation shapes (right-click a shape → Edit Alt Text to find or set its name). Assign this macro to a button: Developer → Insert → Button → draw it → assign macro → label it "Hide Help".

Running your macro

Developer → Macros → select your macro → Run. Or assign it to a button on the sheet (right-click any shape → Assign Macro). Or add it to the Quick Access Toolbar for one-click access from any workbook. Keyboard shortcuts can also be assigned at the time of recording (Developer → Record Macro → Shortcut key).

Security notes

Macros can execute arbitrary code, which is why Excel warns you before running macros from unknown sources. Only enable macros from workbooks you created or trust. When sharing a macro-enabled workbook, save it as .xlsm (macro-enabled workbook) — regular .xlsx files strip all VBA code on save. For workbooks shared with others, consider converting frequently used macros to Add-ins so they run without modifying the shared file.

` }, // ════════════════════════════════════════ // SQL SERIES — 9 articles // ═══════════════════════════════════════