Erlandsen Data ConsultingThe error message “A PivotTable report cannot overlap another PivotTable report” has annoyed me lately, especially when you try to update PivotTables in a workbook with lots of worksheets with multiple PivotTables. Excel is not very helpful since the error message doesn’t include any hints about where those conflicting PivotTables are. With the macros below [...]
The built-in method Workbooks.Add creates a new workbook with a predetermined number of worksheets, usually 3, but this can be another number if the user has changed the setting for how many worksheets a new workbook should contain. I really hate to have empty, unused worksheets in my workbooks, and I am fed up with [...]
Using the macro below it is possible to update / refresh all pivottable reports in a workbook: Sub UpdateAllPivotTableReports(wb As Workbook) Dim ws As Worksheet, pt As PivotTable, lngCount As Long, i As Long If wb Is Nothing Then Exit Sub Application.StatusBar = “Counting pivottables in ” & wb.Name & “…” lngCount = 0 For [...]
The example macro below shows how you can run macros (procedures and functions) that are stored in another open workbook. Sub ExamplesRunMacroInAnotherWorkbook() Dim wb As Workbook, strProcedureName As String, strFunctionName As String Dim varResult As Variant Set wb = Workbooks("MyMacroWorkbookName.xls") strProcedureName = "MyMacroProcedureName" ‘ strFunctionName = "MyMacroFunctionName" ‘ examples Application.Run "WorkbookName.xls!ProcedureName" Application.Run "’" & wb.Name [...]
With the macros below you can save settings/information in a workbook that you can retrieve later. The saved settings will be hidden by default, but it is also possible to store the settings as visible to the user. Sub SaveSettingWBN(strSettingName As String, strSettingValue As String, _ Optional blnVisible As Boolean = False) With ThisWorkbook On [...]
Have you ever forgotten your workbook protection passwords? This tool has been available from this site since the days of Excel version 5 and has helped thousands recover their work, for free. This tool will in a relative short time find a password capable of unprotecting the locked workbook and/or worksheets (this password will probably [...]
The macro below can be used to copy a cell range from one or all worksheets in one or more workbooks to a new workbook. You will need to customize the example macro a little to make it fit your needs. Sub TestCopyDataFromMultipleWorkbooks() ‘ updated 2008-04-30 by OPE Dim varWorkbooks As Variant, wb As Workbook [...]
With the macros below you can find and delete formulas in cells that refers to other workbooks. The macros doesn’t find all external references since they only look in the worksheet formulas. Sub DeleteOrListLinks() Dim i As Integer If ActiveWorkbook Is Nothing Then Exit Sub i = MsgBox(“YES: Delete external formula references” & Chr(13) & [...]
This example workbook contains macros that might be useful if you have problems with users that never remembers to close workbooks they share with other users. Click here to download this file. Updated: 2002-01-26 Requires: XL97 File size: 12 kB
This workbook shows how to attach a CommandBar to a workbook. The CommandBar can be visible only when the workbook it’s attached to is active. Click here to download this file. Updated: 2000-08-08 Requires: XL97 File size: 9 kB