Copy & Paste visible cells only

[Scott]

Is it possible to copy and paste visible cells only?

Thanks


Selection.Copy
'Add a new workbook and copy selected range
Workbooks.Add
Range("a1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

 

[Ed]

In Excel 2000, I use only
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=_
False, Transpose:=False
and it works fine for me. Yes, you'd have to add the Paste Formats to get
that; I didn't bother. But it did paste only the cells visible after
AutoFilter and hiding.

HTH
Ed

 

[Tom Ogilvy]

Unless you data is from a filtered range, you would have to copy only the
visible cells

Edit=>Goto =>Special, select Visible.

Then do your copy

 

[JE McGimpsey]

One way:

Dim rCopy As Range
On Error Resume Next 'in case no visible cells selected
Set rCopy = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rCopy Is Nothing Then _
rCopy.Copy Destination:=Workbooks.Add.Sheets(1).Range("A1")

 

[Scott]

Thank you! That worked great.

One way:

Dim rCopy As Range
On Error Resume Next 'in case no visible cells selected
Set rCopy = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rCopy Is Nothing Then _
rCopy.Copy Destination:=Workbooks.Add.Sheets(1).Range("A1")