WIXLIB

20 USEFUL EXCELMACRO EXAMPLESby Sumit Bansal (Excel MVP)

Using Excel Macros can speed up work and save you a lot of time.While writing an Excel VBA macro code may take some time initially, onceit's done, you can keep it available as a reference and use it wheneveryou need it next.In this Ebook, I am going to list some useful Excel macro examples that Ineed often and have seen other people use frequently.There is also a section where I explain how to use this code. It needs tobe copy pasted into the VB Editor (steps explained in detail later in theebook).https://trumpexcel.comSumit Bansal (Excel MVP)

Topics Covered in the EbookUsing the Code from Excel Macro Examples . 4How to Run the Macro . 5Excel Macro Examples . 61. Unhide All Worksheets at One Go . 62. Hide All Worksheets Except the Active Sheet . 73. Sort Worksheets Alphabetically Using VBA. 84. Protect All Worksheets At One Go . 95. Unprotect All Worksheets At One Go . 106. Unhide All Rows and Columns . 117. Unmerge All Merged Cells . 128. Save Workbook With TimeStamp in Its Name . 139. Save Each Worksheet as a Separate PDF . 1410. Save Each Worksheet as a Separate PDF . 1511. Convert All Formulas into Values . 1612. Protect/Lock Cells with Formulas . 1713. Protect All Worksheets in the Workbook . 1814. Insert A Row After Every Other Row in the Selection . 1915. Automatically Insert Date & Timestamp in the Adjacent Cell . 2016. Highlight Alternate Rows in the Selection . 2117. Highlight Cells with Misspelled Words . 2218. Refresh All Pivot Tables in the Workbook . 2319. Change the Letter Case of Selected Cells to Upper Case . 2420. Highlight All Cells With Comments . 25https://trumpexcel.comSumit Bansal (Excel MVP)

Using the Code from Excel Macro ExamplesHere are the steps you need to follow to use the code from any of theexamples: Open the Workbook in which you want to use the macro. Hold the ALT key and press F11. This opens the VB Editor. Right-click on any of the objects in the project explorer. Go to Insert -- Module. Copy and Paste the code in the Module Code Window.In case the example says that you need to paste the code in theworksheet code window, double click on the worksheet object and copypaste the code in the code window.Once you have inserted the code in a workbook, you need to save it witha .XLSM or .XLS extension.https://trumpexcel.comSumit Bansal (Excel MVP)

How to Run the MacroOnce you have copied the code in the VB Editor, here are the steps to runthe macro: Go to the Developer tab. Click on Macros. In the Macro dialog box, select the macro you want to run. Click on Run button.In case you can't find the developer tab in the ribbon, read this tutorial tolearn how to get it.In case the code is pasted in the worksheet code window, you don't needto worry about running the code. It will automatically run when thespecified action occurs.Now, let's get into the useful macro examples that can help you automatework and save time.Note: You will find many instances of an apostrophe (') followed by a lineor two. These are comments that are ignored while running the code andare placed as notes for self/reader.https://trumpexcel.comSumit Bansal (Excel MVP)

Excel Macro Examples1. Unhide All Worksheets at One GoIf you are working in a workbook that has multiple hidden sheets, youneed to unhide these sheets one by one. This could take some time incase there are many hidden sheets.Here is the code that will unhide all the worksheets in the workbook.'This code will unhide all sheets in the workbookSub UnhideAllWoksheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.Visible xlSheetVisibleNext wsEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

2. Hide All Worksheets Except the Active SheetIf you're working on a report or dashboard and you want to hide all theworksheet except the one that has the report/dashboard, you can use thismacro code.'This macro will hide all the worksheet except the active sheetSub HideAllExcetActiveSheet()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsIf ws.Name ActiveSheet.Name Then ws.Visible xlSheetHiddenNext wsEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

3. Sort Worksheets Alphabetically Using VBAIf you have a workbook with many worksheets and you want to sort thesealphabetically, this macro code can come in handy. This could be the caseif you have sheet names as years or employee names or product names.'This code will sort the worksheets alphabeticallySub SortSheetsTabName()Application.ScreenUpdating FalseDim ShCount As Integer, i As Integer, j As IntegerShCount Sheets.CountFor i 1 To ShCount - 1For j i 1 To ShCountIf Sheets(j).Name Sheets(i).Name ThenSheets(j).Move before: Sheets(i)End IfNext jNext iApplication.ScreenUpdating TrueEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

4. Protect All Worksheets At One GoIf you have a lot of worksheets in a workbook and you want to protect allthe sheets, you can use this macro code.It allows you to specify the password within the code. You will need thispassword to unprotect the worksheet.'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Protect password: passwordNext wsEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

5. Unprotect All Worksheets At One GoIf you have some or all of the worksheets protected, you can just use aslight modification of the code used to protect sheets to unprotect it.'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Unprotect password: passwordNext wsEnd SubNote that the password needs to the same that has been used to lock theworksheets. If it's not, you will see an error.https://trumpexcel.comSumit Bansal (Excel MVP)

6. Unhide All Rows and ColumnsThis macro code will unhide all the hidden rows and columns.This could be really helpful if you get a file from someone else and wantto be sure there are no hidden rows/columns.'This code will unhide all the rows and columns in the WorksheetSub UnhideRowsColumns()Columns.EntireColumn.Hidden FalseRows.EntireRow.Hidden FalseEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

7. Unmerge All Merged CellsIt's a common practice to merge cells to make it one. While it does thework, when cells are merged you will not be able to sort the data.In case you are working with a worksheet with merged cells, use the codebelow to unmerge all the merged cells at one go.'This code will unmerge all the merged cellsSub UnmergeAllCells()ActiveSheet.Cells.UnMergeEnd SubNote that instead of Merge and Center, I recommend using Centre AcrossSelection option.https://trumpexcel.comSumit Bansal (Excel MVP)

8. Save Workbook With TimeStamp in Its NameA lot of time, you may need to create versions of your work. These arequite helpful in long projects where you work with a file over time.A good practice is to save the file with timestamps.Using timestamps will allow you to go back to a certain file to see whatchanges were made or what data was used.Here is the code that will automatically save the workbook in the specifiedfolder and add a timestamp whenever it's saved.'This code will Save the File With a Timestamp in its nameSub SaveWorkbookWithTimeStamp()Dim timestamp As Stringtimestamp Format(Date, "dd-mm-yyyy") & " " & Format(Time, "hhss")ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" ×tampEnd SubYou need to specify the folder location and the file name.In the above code, "C:UsersUsernameDesktop is the folder location I haveused. You need to specify the folder location where you want to save thefile. Also, I have used a generic name "WorkbookName" as the filenameprefix. You can specify something related to your project or company.https://trumpexcel.comSumit Bansal (Excel MVP)

9. Save Each Worksheet as a Separate PDFIf you work with data for different years or divisions or products, you mayhave the need to save different worksheets as PDF files.While it could be a time-consuming process if done manually, VBA canreally speed it up.Here is a VBA code that will save each worksheet as a separate PDF.'This code will save each worsheet as a separate PDFSub SaveWorkshetAsPDF()Dim ws As WorksheetFor Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" &ws.Name & ".pdf"Next wsEnd SubIn the above code, I have specified the address of the folder location inwhich I want to save the PDFs. Also, each PDF will get the same name asthat of the worksheet. You will have to modify this folder location (unlessyour name is also Sumit and you're saving it in a test folder on thedesktop).Note that this code works for worksheets only (and not chart sheets).https://trumpexcel.comSumit Bansal (Excel MVP)

10. Save Each Worksheet as a Separate PDFHere is the code that will save your entire workbook as a PDF in thespecified folder.'This code will save the entire workbook as PDFSub t xlTypePDF,"C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf"End SubYou will have to change the folder location to use this code.https://trumpexcel.comSumit Bansal (Excel MVP)

11. Convert All Formulas into ValuesUse this code when you have a worksheet that contains a lot of formulasand you want to convert these formulas to values.'This code will convert all formulas into valuesSub ConvertToValues()With ActiveSheet.UsedRange.Value .ValueEnd WithEnd SubThis code automatically identifies cells are used and convert it into values.https://trumpexcel.comSumit Bansal (Excel MVP)

12. Protect/Lock Cells with FormulasYou may want to lock cells with formulas when you have a lot ofcalculations and you don't want to accidentally delete it or change it.Here is the code that will lock all the cells that have formulas, while all theother cells are not locked.'This macro code will lock all the cells with formulasSub LockCellsWithFormulas()With ActiveSheet.Unprotect.Cells.Locked d True.Protect AllowDeletingRows: TrueEnd WithEnd Subhttps://trumpexcel.comSumit Bansal (Excel MVP)

13. Protect All Worksheets in the WorkbookUse the below code to protect all the worksheets in a workbook at one go.'This code will protect all sheets in the workbookSub ProtectAllSheets()Dim ws As WorksheetFor Each ws In Worksheetsws.ProtectNext wsEnd SubThis code will go through all the worksheets one by one and protect it.In case you want to unprotect all the worksheets, use ws.Unprotectinstead of ws.Protect in the code.https://trumpexcel.comSumit Bansal (Excel MVP)

14. Insert A Row After Every Other Row in the SelectionUse this code when you want to insert a blank row after every row in theselected range.'This code will insert a row after every row in the selectionSub InsertAlternateRows()Dim rng As RangeDim CountRow As IntegerDim i As IntegerSet rng SelectionCountRow rng.EntireRow.CountFor i 1 To et(2, 0).SelectNext iEnd SubSimilarly, you can modify this code to insert a blank column after everycolumn in the selected range.https://trumpexcel.comSumit Bansal (Excel MVP)