If you are new to workbooks with macros here is a short 2 min video on the quick setup needed to work with them on your PC’s Excel and some basic points in which to be aware.
For those new to workbooks with macros Read More »
Note this only works for Tables. One day I will adapt this code for a 2nd macro to work with a specified range. Following on from my recent post of Fix duplicated Conditional Formatting Rules , I mentioned Debra Dalgaleish’s website page had some great VBA to fix duplicated Conditional Formatting rules. I tweeked this
Macro to fix Duplicated Conditional Formatting rules in Tables Read More »
As might know there is no autocomplete functionality or any other features such as different font sizes with good old Data Validation cell dropdowns. On a client’s project I worked on today we really needed autocomplete capability. The dropdown list was 80 rows long and the values were messy to scroll through some with 3
Autocomplete Workaround for Validation Dropdowns Read More »
My last blog was on accessing personal macros from my custom ribbon. This blog is about where to store these personal macros. These personal macros are the ones that save you time and tedious work. There are basically two ways to go. Some save them to their Personal.xlsb file that loads on opening Excel. Others
Personal Macros – Personal.xlsb or Add-In.xlam? Read More »
I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks. Starting with the first workbook and data set in a Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet. This could
VBA to Change Pivot Table Source Read More »
Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky to get the syntax right for Worksheet formulas. Tricky because string quotes need to be doubled for VBA and this is tedious because unlike a worksheet formula which gives you instant feedback, entering via VBA can
Tip for creating syntax in VBA for Worksheet formulas Read More »
This post is about a common business problem of grabbing messy data from a transactional document and updating a data set to collect history. So in this post we will use VBA arrays to gather messy data ie consolidate it. In my previous life, I was in Commodity Export Marketing, so I am using the
Use VBA Arrays to gather messy data Read More »
I thought I would blog some handy personal macros I use day to day. This is the first and I will do others. This one simply is a macro to select all formulas (so I called it SelectAllFormulas) on the worksheet using the SpecialCells method. Nothing hard in this and I used the macro recorder
Macro to Select All Formulas Read More »
I had a small, interesting job involving exporting to PDF last week where the client had an .xlt template with existing in-house VBA to let the user save certifying documents within the an .xls file to a default folder plus “print to pdf” ie creating a pdf file and saving it to a user chosen