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