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 code just a bit so that all you have to do is select anywhere in any Table in the active worksheet and it …
Macro to fix Duplicated Conditional Formatting rules in Tables 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 …
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 »
Update 11/08/21 Well two interesting recent developments have occurred regarding this post! Microsoft MVP Rick Rothstein kindly contacted me about much more elegant code than I have below, trying to assign 6 table columns to a range variable so the macro would operate on selected table columns (not all). So I have just the relevent …
Check range for specific characters – Customizing the Ribbon 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 …
So this post is the 2nd part of my recent post on how Excel can automate data entry to your accounting system. See here for Part A. This example concerns automating entries on your bank statement you can reliably identify. In my case it was to record bank transfers from my cheque account to an interest …
This is about macro I have used quite often in the last few years that achieves two things: a) toggles to hide or unhide columns set by the user and b) the macro button changes in font colour and text wording to confirm to the user the change made. In range A1:J1 there are labels …
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 …
Further to my recent post this is one of the Business Model Renovation macros used in this project what I call one of my ‘quick and dirty’ but handy and versatile macros. It has been written this way because as I made changes to formulas in the templates I would just wanted to copy these …
If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a print macro. If your application needs to print several dynamic reports it’s even better to have one versatile print routine that is passed the parameters it needs to do the job. One print macro is easier …