Excel VBA – Copy And Paste (Special) Without Using The Clipboard

image

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

One thought on “Excel VBA – Copy And Paste (Special) Without Using The Clipboard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s