After more than ten years and 500 client projects, there is a set of small macros I end up adding to nearly every workbook. None of them is complicated, but together they remove a lot of repetitive clicking. I have put all twelve in one file you can import in a minute.
What is in the pack
- UnhideAllSheets shows every hidden sheet at once, instead of one at a time.
- ProtectAllSheets and UnprotectAllSheets lock or unlock every sheet with one password, with filtering and sorting still allowed.
- DeleteBlankRows removes completely empty rows from the active sheet.
- HighlightDuplicates colours repeated values in any range you select.
- CleanSelectedText trims extra spaces and removes the invisible characters that come with copied web data.
- FormulasToValues replaces formulas with their results before you send a file.
- CreateSheetIndex builds an Index sheet with a clickable link to every tab.
- SplitSheetByColumn gives every region, rep or customer their own sheet.
- CombineAllSheets stacks every sheet into one, with a column showing where each row came from.
- ExportSheetsToPdf saves each sheet as its own PDF in a folder you pick.
- SaveBackupCopy saves a dated copy next to the original before you make big changes.
- ListAllFormulas lists every formula on a sheet, which is very useful when you inherit someone else’s workbook.
Two examples
Unhiding sheets one by one is painful in a workbook with twenty hidden tabs. This does it in one go:
Public Sub UnhideAllSheets()
Dim ws As Worksheet, shown As Long
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible <> xlSheetVisible Then
ws.Visible = xlSheetVisible
shown = shown + 1
End If
Next ws
MsgBox shown & " sheet(s) are now visible.", vbInformation
End SubAnd before you email a report to a client, this turns every formula in the selection into a plain value so nothing breaks when links or source sheets are missing:
Public Sub FormulasToValues()
If TypeName(Selection) <> "Range" Then Exit Sub
If MsgBox("Replace every formula in the selection with its current value?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Selection.Value = Selection.Value
End SubGet all 12 macros
Enter your name and email below and the download starts straight away. Every macro is commented and the file includes step by step instructions.
12 Excel VBA macros that save hours every week
The small macros I use on almost every client file. One .bas file you import once, with clear comments and step by step instructions.
- Unhide, protect and unprotect all sheets
- Delete blank rows and clean messy text
- Highlight duplicates in any range
- Split one sheet into many by a column
- Combine every sheet into one
- Export each sheet to its own PDF
- Clickable sheet index, dated backups and a formula audit list
How to install
- Open your workbook and press Alt + F11 to open the VBA editor.
- Choose File, Import File and pick the downloaded .bas file.
- Save the workbook as Excel Macro-Enabled Workbook (.xlsm).
- Run the macro from Developer, Macros, or press Alt + F8.
Always try a new macro on a copy of your file first, because macro changes cannot be undone with Ctrl + Z.
Need something bigger, like a full reporting system or a tool built around your own data? Tell me what you need.
Common problems and quick fixes
- “Macros have been disabled”. Right-click the file in File Explorer, choose Properties, tick Unblock and press OK. Then open it again and press Enable Content.
- The button does nothing. Check that the file is saved as .xlsm. A normal .xlsx file throws the code away when it is saved.
- A macro stops on a protected sheet. Unprotect the sheet first, or use the Master Control macro from the related guides below.
- It runs slowly on big files. Every macro in the pack switches off screen updating and automatic calculation while it works, then turns them back on.
Frequently asked questions
Do these macros work on a Mac?
Most of them do in Excel 2016 or newer for Mac. The ones that talk to Outlook or open file dialogs need small changes, because Mac Excel handles folders and other apps differently.
Can I put them in my Personal Macro Workbook?
Yes. Import the .bas file into PERSONAL.XLSB and every macro is ready in any workbook you open, without copying the code each time.
Is it safe to run code from the internet?
Read the code before you run it. Every macro here is short, plain VBA with comments, and none of them connect to the internet or delete files.
Related guides
- Send personalised emails from Excel with Outlook
- Combine all CSV files in a folder into one sheet
- Lock and unlock every sheet with one button
- Split one sheet into many sheets by column value
Need this built for your own workbook? I write macros like these for US businesses every week, tested on a copy of your file and delivered with a short how-to video. See my Excel VBA automation service or get a free quote in under a minute.
