Excel Agility: Macro Basics Part 3

Working with multiple worksheets in VBA macros is essential for automating multi-tab reporting and data consolidation tasks. You can reference a specific sheet by name using Worksheets("Sheet1") or by index using Worksheets(1). To loop through all sheets in a workbook, use a For Each loop: For Each ws In ThisWorkbook.Worksheets. Inside the loop, you can read or write data, apply formatting, or copy ranges from each sheet to a summary. To create a new sheet, use Worksheets.Add and set its Name property. To copy a sheet, use Worksheets("Source").Copy After:=Worksheets(Worksheets.Count) to place it at the end of the workbook. Deleting sheets requires temporarily suppressing the confirmation prompt with Application.DisplayAlerts = False before the delete command. Mastering worksheet manipulation in VBA is critical for professionals who produce monthly reports requiring identical operations across many tabs, and is a core topic in Aurora Training Advantage's Excel Agility: Macro Basics training series.

VBA provides full control over workbook file operations, enabling automation of common tasks like opening source files, processing data, and saving output. To open a workbook, use Workbooks.Open("C:\path\to\file.xlsx"), which returns a Workbook object you can assign to a variable for later reference. To save a workbook, use workbook.Save (overwrite) or workbook.SaveAs("new_path.xlsx") to save to a new location. To close a workbook, use workbook.Close SaveChanges:=True (or False to close without saving). A common pattern is to open a source data file, copy specific data into the active workbook, then close the source without saving. Using ThisWorkbook refers to the workbook containing the running macro, while ActiveWorkbook refers to whichever workbook is currently in focus—an important distinction to avoid inadvertently writing to the wrong file. These file operation techniques are foundational for VBA automation workflows that process multiple files in a batch process or automated reporting pipeline.

One of the most critical techniques in Excel VBA is finding the last row of data dynamically so that macros work correctly on datasets of any size. The most reliable method is: lastRow = Cells(Rows.Count, 1).End(xlUp).Row — this starts at the very last row of column A and moves upward until it finds the first non-empty cell, returning that row number. This approach handles gaps in data gracefully and works regardless of how many rows exist. You can then use lastRow in loops or range definitions: For i = 2 To lastRow processes every data row. An equivalent approach using the UsedRange property works for finding the extent of the entire used area of the sheet. Avoid hard-coding row numbers like 1000 in macros—data that exceeds the hard-coded limit will be silently ignored, causing incorrect results. Dynamic row detection is a non-negotiable best practice for any VBA macro intended to run on data that changes size between executions.

VBA event procedures run automatically when specific actions occur in Excel—without the user needing to manually trigger them. Common workbook-level events include Workbook_Open (runs when the file opens), Workbook_BeforeSave (runs before saving), and Workbook_BeforeClose (runs before closing). Worksheet-level events include Worksheet_Change (runs when any cell value changes), Worksheet_SelectionChange (runs when the user selects a different cell), and Worksheet_Activate (runs when the sheet is selected). Event procedures are stored in the workbook's VBA project—workbook events in ThisWorkbook, worksheet events in the respective sheet module. For example, a Worksheet_Change event can automatically validate or reformat a cell immediately after a user edits it, or trigger a calculation update without requiring a macro button. Events must be used carefully—they fire even when macros themselves change cell values, potentially causing infinite loops if not controlled with Application.EnableEvents = False during automated operations. Understanding events transforms macros from manual tools into responsive, integrated spreadsheet behaviors.

Copying data between worksheets via VBA is one of the most common automation tasks in Excel. The direct method uses Range.Copy followed by Range.PasteSpecial to control what gets pasted—values only, formats, formulas, or all content. For example: Worksheets("Source").Range("A1:D100").Copy then Worksheets("Dest").Range("A1").PasteSpecial xlPasteValues copies values without formatting. A more efficient approach for large datasets avoids the clipboard entirely by directly assigning values: Worksheets("Dest").Range("A1:D100").Value = Worksheets("Source").Range("A1:D100").Value. This is significantly faster because it doesn't use the clipboard. For dynamic ranges, combine the direct assignment with a lastRow calculation to copy only the populated rows. After copying to the clipboard, always call Application.CutCopyMode = False to clear the clipboard marquee. These techniques are essential for macros that consolidate data from multiple source sheets into a single summary report, a core pattern in monthly financial reporting workflows.