≡ Menu

Macro

Macro to wrap IFERROR() around formula in cell or range

This is a fantastic little macro that I picked up from The Spreadsheet Guru I seem to use the GETPIVOTDATA function on a near daily basis to generate fixed format reports, it is such a flexible tool for creating dynamic reports that are formatted just as you require - without the limitations of the original [...]

{ 1 comment }

Macro to convert filtered list to multiple pdf

Macro to convert filtered list to multiple pdf Thumbnail

We generate reports that hold details for multiple practices all in one sheet, which is nice and convenient for me as I can run a pivot table off it and analyse any number of practices and services as I see fit. Unfortunately this report also needs to go out to the individual practices and without [...]

{ 4 comments }

Excel Pivot Table or Crosstab to Flat List

Excel Pivot Table or Crosstab to Flat List Thumbnail

I often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across the [...]

{ 17 comments }

What to do with the Personal Macro Workbook

What to do with the Personal Macro Workbook Thumbnail

When you first start to dabble with macros and VBA, one of the first challenges you will face is fathoming out what to do with the personal macro workbook. The Personal Macro Workbook called personal.xlsb does not exist until you create it and even then it tends to remain hidden from general view. This personal [...]

{ 2 comments }

How to Use Excel VBA to Log-In to a Website

How to Use Excel VBA to Log-In to a Website Thumbnail

I've been tasked with downloading regular data updates from a national reporting website but as this website has user log-in functionality. This means I have to set the macro up to open the webpage and then automatically enter the user ID and password. I found a very useful tutorial on accessing google webpages via VBA, [...]

{ 1 comment }

Refresh Multiple Pivot Tables – The Easy Way

I use pivot tables a lot. They drive multiple reports in my workbooks and it is not unusual for me to have 6 or so pivots in each spreadsheet. When the underlying data changes I spend quite a lot of time clicking through each pivot table to refresh it. I was happy to discover the [...]

{ 0 comments }

Macro to Insert a “Last Update On:” Date Value

Macro to Insert a “Last Update On:” Date Value Thumbnail

I have a number of summary reports that are updated by the use of a macro update button. I usually include an area on the report to include a datestamp that indicates when the report was last updated or refreshed. This is the code I use: [cc lang="vb"]Dim VERSIONDATE As String VERSIONDATE = Format(Now, "dd-mm-yy [...]

{ 0 comments }
Macro to Consolidate Data Ranges from Multiple Excel Spreadsheets Thumbnail

I have a lot of macro workbooks that consolidate multiple data ranges from individual tabs in the same workbook into one summary tab but today I discovered how to consolidate data ranges from different excel workbooks into one summary workbook. My task today was to consolidate one of the worst NHS SLAM reports that we [...]

{ 2 comments }