You may download this Microsoft Excel VBA Add-In „Helpers 1.0“ and use it under the terms of GNU General Public License Version 3. To Install, save and unzip, then follow the isntructions in the „Readme Helpers v1.0.docx“.
Download Excel Helpers v1.0 Add-In
This is a summary of the new menu items available withe the Microsoft Excel VBA Add-In.
File
- Save all visible workbooks: will save all visible workbooks.
- Paste save date into cell: will paste the save date of the workbook into the active cell.
- Merge sheets from all files in folder: will merge cells from all worksheets from each workbook in a folder.
Insert
- Copy and insert columns: will copy the column of the active cell and paste this column n-times right of the active cell.
- Copy and insert rows: will copy the row of the active cell and paste this column n-times below the active cell.
Format
- Center cells across selection: it is often better to “Center Across Selection” instead of using the “Merge Cells” command.
- Paste hyperlinks to all sheets: will overwrite the selected cell and some cells below with a list of hyperlinks to each worksheet in this workbook.
Layout
- Format sheet in landscape: will format the sheet in landscape format including a footer
- Format sheet in portrait: will format the sheet in portrait format including a footer
Show
- Set zoom factor for all sheets: will set a zoom factor between 10 and 400 percent to all sheets in a workbook
- Unhide all sheets: will unhide all invisible sheets in a workbook.
- Unhide all names: will unhide all invisible names in a workbook.
Calculate
- Calculate selected cells: in case the calculation mode is set to manual, this will only calculate the selected cells to avoid lengthy calculations in large files.
- Calculate, update pivots, save: will perform a calculation for large sheets. Use if calculation mode has been set to manual.
- Rebuild index, calculate, update pivots, save This will perform a calculation for large sheets. Use if calculation mode has been set to manual.
Pivot
- Switch pivot ‚Count‘ and ‚Sum‘: will switch the aggregation mode for pivot values between “sum” and “count”. For testing, there is a file called “TestPivot.xlsx” in the main folder.
- Count pivot caches: a pivot cache is a container that holds a static copy of the source data in memory. To reduce memory usage, copy existing pivot tables instead of creating new ones.