I tend to find myself prototyping tools for our users within Excel, by far and away their favourite tool. I have a nice little wrapper for exposing C# functions as Worksheet functions and a slowly growing universe of VBA snippets for making automation and creation of these workbooks quickly.
Most of the time these spread sheets are parsing input data files, reshaping and spitting them out. I have started using Alteryx for a lot of this boring data transformation and preparation. Its a fantastic tool but not everyone has access to it. The guys at The Information Lab run a fantastic introductory course on Alteryx so if this something you end up doing a lot well worth looking into.
Anyway back to Excel and VBA. I always stick by a few basic rules when automating spread sheet (nothing revolutionary but tends to mean I don’t have to do much maintenance on the sheets):
- Always have ‘Option Explicit’ at the top of a file. Forces you to declare variables but this is a good thing.
- Use named ranges rather than fixed addresses. Keeps the VBA code reasonably separate from the spread sheet layout.
- Never use the Active Cell, Sheet , Workbook or the Selection. Too easy for users to accidently be in wrong place.
- Avoid the clipboard. Again far too easy to muck up automation.
The only one of these that needs any extra work is how to copy and paste data around a spread sheet. One of the features that VBA allows is copying data without going through the clipboard.
Copy and Paste (via Clipboard)
The simplest way via the clipboard:
Sub SimpleCopy() Dim src As Range Set src = ThisWorkbook.Names("Src").RefersToRange Dim dest As Range Set dest = ThisWorkbook.Names("Dest").RefersToRange src.Copy dest.PasteSpecial xlPasteAll Application.CutCopyMode = False End Sub
This copies everything as is but doesn’t copy the column widths. The last line ‘Application.CutCopyMode = false‘ clears the clipboard. Adding one extra line achieves copying the widths:
Sub SimpleCopyWidths() Dim src As Range Set src = ThisWorkbook.Names("Src").RefersToRange Dim dest As Range Set dest = ThisWorkbook.Names("Dest").RefersToRange src.Copy dest.PasteSpecial xlPasteAll Application.CutCopyMode = False End Sub
Both of these use the clipboard. It is easy to amend so that values are pasted of just values by changing the xlPasteAll. The different values can be found here, and the general documentation on PasteSpecial here.
Avoiding the Clipboard
To reproduce the behavior of the first version without using the clipboard is actually very straight forward. The Range.Copy command takes a parameter of the target range to paste:
Sub CopyWithoutClipboard() Dim src As Range Set src = ThisWorkbook.Names("Src").RefersToRange Dim dest As Range Set dest = ThisWorkbook.Names("Dest").RefersToRange src.Copy dest End Sub
Copying values is a little more involved. I resize the destination range to be the same side as the source and then can set the value of the cells via the Value property. Please note, this only copies the values it does not copy any formatting.
Sub CopyValuesWithoutClipboard() Dim src As Range Set src = ThisWorkbook.Names("src").RefersToRange Dim dest As Range Set dest = ThisWorkbook.Names("dest").RefersToRange dest.Resize(src.Rows.Count, src.Columns.Count).Value = src.Value End Sub
Copying Column Width is easy enough as you just need to iterate over the columns and set each width individually. Something like:
Sub CopyColumnWidthsWithoutClipboard() Dim Src As Range Set Src = ThisWorkbook.Names("Src").RefersToRange Dim Dest As Range Set Dest = ThisWorkbook.Names("Dest").RefersToRange Dim colIdx As Integer For colIdx = 1 To Src.Columns.Count Dest.Columns(, colIdx).ColumnWidth = Src.Columns(, colIdx).ColumnWidth Next End Sub
Unfortunately copying just the formats or formulas of the source range is a lot harder. In this case, it needs to be a bit more of a bespoke build for the case. One very useful trick is FormulaR1C1 property of a range as this allows the formula to be set on multiple cells simultaneously. It also tends to be constant as you move across rows or columns.
Copying a Distinct or Filtered List
One other useful little trick I recently discovered was using the AdvancedFilter method. It can be used to create a unique in a target range:
Sub CopyUnique() Dim src As Range Set src = ThisWorkbook.Names("uniqueSet").RefersToRange Dim dest As Range Set dest = ThisWorkbook.Names("Dest").RefersToRange src.AdvancedFilter xlFilterCopy, , dest, True End Sub
You can also apply a filter by supplying a second range with a set of criteria to apply to the data to filter it. There is a good description of using the criteria range at http://www.excel-easy.com/examples/advanced-filter.html