📎 This article includes 1 downloadable practice file ↓

The most common bug I fix in other people’s macros isn’t a typo. It’s this: the macro works when you run it from the right sheet and quietly wrecks a different sheet when you don’t. The cause is almost always ActiveSheet, Range("A1") with no sheet in front, or Workbooks(1).

This lesson is about pointing your code at exactly the workbook and sheet you mean, every time.

Workbooks: ThisWorkbook vs ActiveWorkbook

• ThisWorkbook — the file that contains the code. Never changes while the macro runs.
• ActiveWorkbook — whichever file is in front right now. Changes the moment your macro opens another file.

Rule of thumb: use ThisWorkbook for your own file and keep a variable for any other file you open.

Always hold sheets in variables

Dim wsData As Worksheet, wsOut As Worksheet
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsOut = ThisWorkbook.Worksheets("Summary")

wsOut.Range("B2").Value = Application.WorksheetFunction.Sum(wsData.Range("E:E"))

Every range now says which sheet it belongs to. The macro works whatever sheet is selected — and runs faster because nothing gets selected or activated.

Loop through every sheet

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        ws.Range("A1").Font.Bold = True
        Debug.Print ws.Name, ws.UsedRange.Rows.Count
    End If
Next ws

Add, rename and delete sheets

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws.Name = "Report " & Format(Date, "dd-mmm")

Application.DisplayAlerts = False      ' skip "are you sure?"
ThisWorkbook.Worksheets("Old").Delete
Application.DisplayAlerts = True

Does a sheet exist?

Function SheetExists(ByVal nm As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Not wb.Worksheets(nm) Is Nothing
    On Error GoTo 0
End Function

Copy a sheet to a new file and save it

Sub SaveSummaryAsFile()
    Dim newWb As Workbook, path As String
    ThisWorkbook.Worksheets("Summary").Copy          ' no Before/After = new workbook
    Set newWb = ActiveWorkbook                        ' the new file is now active

    path = ThisWorkbook.Path & "\Summary " & Format(Date, "yyyy-mm-dd") & ".xlsx"
    newWb.SaveAs Filename:=path, FileFormat:=xlOpenXMLWorkbook
    newWb.Close SaveChanges:=False
    MsgBox "Saved " & path
End Sub

Formulas that pointed to other sheets now point back to the original file. If the copy should stand alone, convert to values first: newWb.Worksheets(1).UsedRange.Value = newWb.Worksheets(1).UsedRange.Value.

Open another workbook, read it, close it

Sub ReadBankStatement()
    Dim f As Variant, wb As Workbook
    f = Application.GetOpenFilename("Excel files (*.xls*), *.xls*", , "Pick the statement")
    If f = False Then Exit Sub                       ' user pressed Cancel

    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(f, ReadOnly:=True)
    wb.Worksheets(1).UsedRange.Copy ThisWorkbook.Worksheets("Import").Range("A1")
    wb.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

Open read-only whenever you only need to read. It can’t be locked by someone else’s open copy and you can’t save over it by accident.

Save a dated backup of your own file

ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Backup " & Format(Now, "yyyy-mm-dd hhmm") & " " & ThisWorkbook.Name

SaveCopyAs writes a copy and leaves you working in the original — ideal at the start of any macro that deletes or overwrites data.

Where people go wrong

Practice

The workbook contains three region sheets and buttons to: list all sheets with row counts, add a dated report sheet, consolidate the regions into one sheet, export the summary as a dated .xlsx, and make a timestamped backup.

Next: events — macros that run by themselves when someone edits a cell, opens the file or selects a sheet.