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