Copy only visible cells

Question

I'm trying to copy visible cells after using my filter to another sheet in the same book but I'm not sure about this code. How it looks like now:

     Sub Button1_Click()
        Dim i As Integer
        Dim VisRan As Range
        VisRan = Sheets(1).Range("a39:bm29684").SpecialCells(xlCellTypeVisible)
        Visran.Copy   
        Sheets(2).Cells(1, 1).Select
        Selection.Paste

But it doesn't work. What's the problem?
Thx in advance

You have no **With** and you have no **Copy** – Gary's Student Mar 19 '14 at 11:34 — Gary's Student, Mar 19 '14 at 11:34
@Gary'sStudent sry I accidentally have deleted this part – Seya Mar 19 '14 at 11:37 — Seya, Mar 19 '14 at 11:37

score 2 · Accepted Answer · answered Mar 19 '14 at 11:46

Try this one:

Sub Button1_Click()
    Dim i As Integer
    Dim VisRan As Range
    On Error Resume Next
    Set VisRan = Sheets(1).Range("a39:bm29684").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If VisRan Is Nothing Then
        MsgBox "There is no visible rows"
        Exit Sub
    End If
    VisRan.Copy Destination:=Sheets(2).Cells(1, 1)
End Sub

Copy only visible cells

1 Answers1