Copy non blank cells to another worksheet

[JD]

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]

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.

 

[JD]

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.