Connect Slicer to Multiple Data Sources

• May 19, 2020
• Marcus Small

Connect Slicer to multiple data sources

Full and Part Summaries with the Dictionary

• June 1, 2017
• Marcus Small

Creating summaries using the Excel VBA scripting dictionary is something you will want to learn. The dictionary is powerful and can summarise data very quickly. This blog post takes you through the process of using the Excel Scripting Dictionary.

Excel VBA Autofilter on Multiple Criteria

• June 7, 2016
• Marcus Small

Anyone who has followed my posts on Ozgrid or Chandoo forum will have noticed how frequently I use the autofilter in favour of any kind of loop.  I have been lead to believe that in some instances that arrays will perform faster than the humble autofilter however for brevity of code and swiftness I really like the simple elegance of the autofilter.  In the article Autofilter on Multiple Conditions I delved into the world of the filer using more than one criteria.

Excel Import Data from Multiple Cells

• April 9, 2016
• Marcus Small

Recently a client asked me if I could create an Excel VBA procedure which picked up data from a file but the data came from multiple sheets and multiple cells in non sequential locations. Firstly, I thought the best method to do this would be to have a summary sheet which is hidden and simply pick this sheet up and consolidate it in the parent workbook.  However the problem was the files had already gone out and over 200 Excel files needed to be consolidated into a single workbook.

Adding Mulitple Criteria in a Single Column

• August 9, 2015
• Marcus Small

Creating a sum where the criteria is in the same column has usually been about creating a sumif or sumifs and adding multiple iterations together.  This turns formula which intuitively should be short into long, difficult to read formulations.  With the intelligent use of Sumproduct you can use one formula to capture multiple criteria in the same column.  Here is an example.

Copy Multiple Sheets with VBA

• July 5, 2015
• Marcus Small

I was asked during a webinar recently how you send multiple worksheets to a new workbook in a batch.  I was pretty sure this information would be on my site but a quick search of my site did not reveal any joy.

I put together a simple file which sends an output sheet and two source data sheets to a directory saves it then starts the process again after changing a unique identifier.  The process is a little more in-depth than sending just one sheet but there is not a great deal more code involved.