I have always found this annoying, but never thought there was a good way to do it.

Thanks for sharing this.

Hi Hivishy, I've used this amazing function a number of times but for some reason in the latest context I get the following error message: Run-time error '1004': Application-defined or object-defined error The odd thing is that the code works - i.e. the data is copied to the target area but then the error message appears. Here is the line of code: Call CopyValues(Worksheets("OpenOrders").Range("A:K"), Workbooks(ReconcileFile).Worksheets("GIMII").Range("A1")) Any ideas of why this might be happening? Thanks for your help, Emmeline Butt

Great advice.
I just have one issue using this procedure: the PasteSpecial xlPasteValues method keeps the formatting of the original data, which is not the case with the procedure you propose. Some of my data is automatically (and incorrectly) converted to dates after "pasting". How can I avoid that and keep my data in its original format?

Thanks

When assigning to the .Value property from the .Value property, numbers stored as text are copied and converted to numbers (just as it would if you manually typed them in), which is not always desirable.

To get it right, one must assign it to the Range object directly:
Range("ToRng") = Range("FromRng")
However, for some reason that only works one cell at a time. I had to implement a double For loop to do it for each cell. I thought performance was to going to degrade, but it held up pretty good.

Improved code to copy without clipboard. Copy the following code and call 'CopyPasteWithoutClipboard' from any module. As an example, a 'Test' procedure is given in the end.

Enum PasteAs
copyByValue = 1
copyByFormula = 2
End Enum

Sub CopyPasteWithoutClipboard(rngSource As Range, rngTarget As Range, lngPasteType As PasteAs, Optional blnTranspose As Boolean = False)

'Do not use this procedure with filtered/hidden rows as it considers all hidden/filtered cells
'While transposing only values can be transposed not formulae
'Merged cells are not considered

Dim lngCalc As Long
Dim lngEvents As Long

With Application
lngCalc = .Calculation
lngEvents = .EnableEvents
If Not .EnableEvents = False Then .EnableEvents = False
If Not .Calculation = xlCalculationManual Then .Calculation = xlCalculationManual
End With

Select Case lngPasteType
Case copyByValue
If blnTranspose Then
rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = Application.Transpose(rngSource.Value)
Else
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End If
Case copyByFormula
If blnTranspose Then
rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = Application.Transpose(rngSource.Value)
Else
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Formula = rngSource.FormulaR1C1
End If
End Select

With Application
If Not .Calculation = lngCalc Then .Calculation = lngCalc
If Not .EnableEvents = lngEvents Then .EnableEvents = lngEvents
End With
End Sub

Sub Test()
Call CopyPasteWithoutClipboard(Sheet1.Range("A1").CurrentRegion, Sheet2.Range("A1"), copyByValue)
End Sub

Thanks