HI, can anyone point out why this isn't working?

Sub CopyDataOnly()

Sheets("Sheet1").Range("A10:A100").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

I end up getting an error after "Selection.Copy".

Basically I'm looking to select all cells with data and move them to
another worksheet and paste them.

Hi JD,

Try the following version which avoids selections:

'=============
Sub CopyDataOnly()
On Error Resume Next
Sheets("Sheet1").Range("A10:A100"). _
SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("Sheet2").Range("A10").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
On Error GoTo 0
Application.CutCopyMode = False
End Sub
'<<=============

Selections are rarely necessary or desirable.

---
Regards,
Norman



"JD" wrote in message
oups.com...
HI, can anyone point out why this isn't working?

Sub CopyDataOnly()

Sheets("Sheet1").Range("A10:A100").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

I end up getting an error after "Selection.Copy".

Basically I'm looking to select all cells with data and move them to
another worksheet and paste them.

Norman Jones wrote:
Hi JD,

Try the following version which avoids selections:

'=============
Sub CopyDataOnly()
On Error Resume Next
Sheets("Sheet1").Range("A10:A100"). _
SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("Sheet2").Range("A10").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
On Error GoTo 0
Application.CutCopyMode = False
End Sub
'<<=============

Selections are rarely necessary or desirable.

Thanks Norman, that works a treat.