Advanced Excel for Productivity

VBA and Macros Reference

Referencing Workbooks Loops and Logic

ThisWorkbook The workbook that is currently running code For n = 1 to 100 … Next n Loop a fixed number of times; iterates n
ActiveWorkbook The workbook that is active (has a cell selected) Do While [condition] … Loop Loop as long as [condition] is true
Workbooks("[Link]") The open workbook called [Link] While [condition] … Loop Loop as long as [condition] is true
[Link]("C:\Path\[Link]") Open a workbook file Do Until [condition] … Loop Loop until [condition] is true
Do … Loop While [condition] Run at least once; loop while [condition] is true
Referencing Sheets For Each wb in Workbooks … Next wb Loop through each open Workbook
ActiveSheet Currently active sheet For Each s on Worksheets … Next s Loop through each Sheet in a Workbook
WorkSheets("name") Reference a sheet by its name (label) For Each c in Selection … Next c Loop through each Cell in the selected range
[Link]("name") Same as above; "ActiveWorkbook" part is optional For Each pt in PivotTables … Next pt Loop through each PivotTable on active sheet
Sheets(1) The first sheet in order; changes if you rearrange sheets If [condition] Then [do this] Else [do that] EndIf If condition is true, execute a set of commands
Sheet1 / Sheet2 / Sheet3 / etc Best way to always reference the same sheet If [cond1] Then [s1] ElseIf [cond2] Then [s2] EndIf Multiple If conditions and multiple statements
[Link] Activates Sheet1; even if you rename or rearrange sheets Exit Sub / Exit Function Stop executing Macro or Function

Referencing Cells Math

Range("C2") Cell C2; not best practice since cells can move around [Link]() / [Link]() Minimum or Maximum of multiple values
Cells(2,3) Cell in row 2, column 3, which is C2 [Link](X, n) Round value X to n significant digits
Range("named_range") Best way to reference cells; use a named reference cell [Link](X) Absolute value of X
ActiveCell Currently active single cell (part of Selection)
Selection Selected cell(s) Optimize for Speed
Columns("A:B") Reference entire columns; also works as Columns(1) [Link] = False Macro runs faster by not updating the screen
Rows("1:2") Reference entire rows; also works as Rows(1) [Link]=xlCalculationManual Manual calculation mode; can make macro faster
.EntireRow Reference the entire row; e.g. [Link] [Link] Need this to force a calculation in Manual mode
.EntireColumn Reference the entire column; e.g. [Link] [Link]=xlCalculationAutomatic Automatic calculation; this is the default

Actions on Sheets/Cells Workbook Properties and Actions

.Select Sheet or cell becomes the current selection .Name Name of the file, with extension ("[Link]")
.Activate Same as .Select .Path Directory path of the file ("C:\Folders")
.Offset(A, B) Offset (move) the selection by A rows and B columns .FullName Path + Name of file ("C:\Folders\[Link]")
.Resize(A, B) Resize the selection to A rows by B columns size .Save Save the workbook
.AutoFit Autofit height or width (of entire rows or columns) .SaveAs Save As, specify file name and extension type
.RowHeight Change height of row/cell .Close Close the workbook
.ColumnWidth Change width of column/cell .RefreshAll Refresh all connections in workbook
.Delete Delete cell(s)
.ClearContents Clear values/formulas from cell (same as DEL key) Workbook Objects
.ClearFormats Clear all formatting from cell (colors, borders, number) .Sheets / .Worksheets Sheets (can use with For Each)
.Copy / .Cut Copy or Cut the selection (use with cells / ranges) .Charts Charts (can use with For Each)
.Insert Insert cells (rows or columns) above or to the left .Names Named references (can use with For Each)
[Link] Regular Paste of cut/copied range .Connections External data connections (can use with For Each)
[Link] Paste:=xlPasteValues Paste Special; in this case just values
[Link] = False Turns off Cut/Copy of selection Pivot Tables
[Sheet1].Copy Copy Sheet1 to a new workbook by itself [Link] = False Macro runs faster; useful before any Pivot changes
[Sheet1].Move Move Sheet1 to a new workbook by itself PivotTables(1).RefreshTable Refresh pivot table (first pivot on sheet)
[Sheet1].Copy after:= [Sheet2] Copy Sheet1 and paste it after Sheet2 PivotTables(1).PivotFields("Category") Reference to pivot field Category
[Sheet1].Move before:= [Sheet3] Move Sheet1 before Sheet3 .PivotFields("Category").PivotItems("Blue").Visible Filter on or off (True or False) for Category = Blue
.PivotFields("Category").CurrentPage = "Blue" Switch filter to only Category = Blue
Properties of Cells .PivotFields("Category").ClearAllFilters Unfilter Category (i.e. include all values)
.Value Value of cell
.Formula Formula of cell; if there's no formula then same as value Keyboard Shortcuts
.Address Address of cell, e.g. "$A$2" or "$B$5" ALT + F11 Switch between VBA editor and Excel
.[Link] Set font size (can also set [Link]; [Link]; etc) F5 Run VBA macro
.[Link] Set cell background color F9 Insert or remove break point
.Borders(xlEdgeTop).LineStyle = True Add regular border to the bottom edge of cell F8 Execute next line

[Link]