Object Variable Not Set

Hello Mark,

If you only need values to be copied then the quickest way is to use an array. The system performs a "block transfer" with arrays. That is. all the data is written at once and not a single element at a time. You can copy the range into a Variant variable without having to dimension the rows and columns to be used. This method only works with the Value of a range.

 'This example copied the range A1:C10 from "Sheet1" to "Sheet2" starting at A1.

  Dim arrData As Variant
  Dim Rng As Range

    Set Rng = Worksheets("Sheet1").Range("A1:C10")
    arrData = Rng.Value

    Worksheets("Sheet2").Range("A1").Value = arrData