Clean pastes with Excel VBA

I’ve talked about some of the Excel VBA I’ve done before, and here’s a new thing I learned today.

Problem

One part of my application selects a range from one worksheet in the spreadsheet and pastes it onto another. Originally, the code I used looked like this:

destinationWorksheet.Range("A2").Select
ActiveSheet.Paste</pre>

There is one problem I was unaware of. Every time the .Paste operation executed, it added a new "Named Range" to the ActiveSheet. I only realized this when snooping around in the Properties for the Excel file, where I found (to much dismay) 840 different "Named Ranges" referring to data long since thrown away! An older version of my application, which used this unspecified Paste operation much more frequently, had even more of these attached to numbers of other worksheets.
Solution

The first thing I looked into was the properties of the .Paste method itself. Turns out there are two of them, "Destination" and "Link," and they are more or less mutually exclusive. The Excel VBA Help provides good explanation of that which I won't reproduce here.

The reason my old call for an unparameterized .Paste worked was that it simply used the current selection as the "Destination" property. This is explained from this portion of the Help document:

Destination Optional Variant. ... If this argument is omitted, the current selection is used.

I had assumed that this parameter was irrelevant. The increase in these weird "Named Ranges" made me suspicious.

I modified my .Paste call this way to see if it would make a difference:
ActiveSheet.Paste Destination:=ActiveCell
Adding a parameter that specifies the default parameter anyway isn't a pretty fix, would it work? After executing a few calls to the .Paste statement in question, I was shocked! It did make a difference! No more "Named Ranges" were being appended to the ActiveSheet.

Cleanup
Wonderful, now I had 840 "Named Ranges" stuck in my file for no reason, referring to data long since wiped off the sheet. Some quick Help file searching taught me some things.
Each Worksheet object in a Workbook contains a collection of Name objects called Names. The Name objects in this collection are the "Named Ranges" in question. With some quick 'n dirty coding, I wiped them all out. Solution is below:
Private Sub NameDR4ng3z()
    ThisWorkbook.Worksheets("destinationWorksheetName").Activate

    For Each myName In ActiveSheet.Names 'list '
        Debug.Print myName.Name
    Next myName

    For Each myName In ActiveSheet.Names 'delete '
        myName.Delete
    Next myName

    Debug.Print "Names deleted? We'll see"
    For Each myName In ActiveSheet.Names 'double-check '
        Debug.Print myName.Name
    Next myName
End Sub
This worked. I'm happy.