Commonly used Excel VBA snippets

Below is collection of frequently used Excel VBA snippets I have accumulated over the years. This page will grow overtime… Feel free to comment and share!

Loop through all sheets in the active workbook and print name

Sub PrintSheetNames()
  For i = 1 To Sheets.Count
    'Print name on each cell, or replace line below and insert new code to be executed on each sheet
    Cells(ActiveCell.Row + i - 1, ActiveCell.Column) = Sheets(i).Name 
  Next i
End Sub

Delete all shapes in a workbook

I use this subroutine because sometimes my workbook would be so cluttered by hundreds shapes that are not selectable using the Goto->Special window. Since deleting them one by one was impractical, I wrote this macro to cycle through the entire workbook for all sheets and delete every shape in each sheet.

Sub DeleteAllShapes()
  For i = 1 To Sheets.Count
    For Each S In Sheets(i).Shapes
      S.Delete
    Next S
  Next i
End Sub