What are the best ways to move excel ranges around using VBA? For as frequently as I need to do it, I still have a lot of trouble with it. I'd like to show my most-used approaches for criticism and/or suggestions of new ways to go about moving ranges.

The cut/copy method seems to be really finicky, especially when multiple worksheets or variables are involved. For example:

```
    Dim wkb1 as Workbook
    Dim wks1 as Worksheet, wks2 as Worksheet

    Set wbk1 = ThisWorkbook
    Set wks1 = wbk1.Worksheets(1)
    Set wks2 = wbk1.Worksheets(2)

    wks1.Range("A1:A5").Copy (wks2.Range("A1"))
        ' Error: Object variable or With Block variable not set.

    wbk1.Worksheets(1).Range("A1:A5").Copy (wbk1.Worksheets(2).Range("A1"))
        ' This works.

```

I would think that avoiding the clipboard increases execution speed. Here, I simply equate one range to another. The drawback is that it requires ranges of equal dimensions:

```

' Copy a range of two equal dimensions.
    WrkSht2.Range("A1:F" & (rngEndRange.row - 10)).Value = _
        WrkSht2.Range("A10", rngEndRange).Value

```

I like the idea of using collections, but there's the draw back of added complexity from needing to work with loops.

```

Sub UseCollection()

    Dim MySheet As Worksheet
    Dim lngLastRow As Long
    Dim rngSearch As Range, rngCell As Range, rngCopy As Range
    Dim MyCollection As New Collection

    Set MySheet = ThisWorkbook.Worksheets(1)
    Set CopySheet = ThisWorkbook.Worksheets(3)

    lngLastRow = MySheet.Range("A1").End(xlDown).Row
    Set rngSearch = MySheet.Range("A1:A" & lngLastRow)
    Set rngCopy = CopySheet.Range("A1")

    For Each rngCell In rngSearch
        MyCollection.Add rngCell.Value
    Next rngCell

    i = 1
    For Each Element In MyCollection
        rngCopy(i, 1) = Element
        i = i + 1
    Next Element

End Sub

```

So how do you all go about copying ranges? Ideally, an approach should be easy to use with worksheet/range variables, and it should be relatively simple.