Thread: copy data if it exists

I don't understand why my macro is failing.

I'm pulling the detail from a pivot table, then filtering out certain criteria. I want to copy anything under the headers, but not the headers themselves. If the available info is ONLY the headers, I don't want to copy anything. My code is breaking down when only headers are available. If I run the code below, the answer that come back is 1 (since only the headers are visible)

Dim rng As Range
Set rng = ActiveSheet.UsedRange

MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count

So, my code to run is set up below. However, for the "only headers" range (1 above), my code fails on the COPY line, which I don't think should even be read since 1 is not greater than 1. What do I have wrong?

Set rng = ActiveSheet.UsedRange
            If Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
            'copy the detail to Today's sheet
            ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).EntireRow.Copy Destination:=Exp.Sheets("Today").Range("A65536").End(xlUp).Offset(1, 0)
            Else
            End If

This is a similar code that i use, which works. Maybe you can adapt your code accordingly.

With ActiveSheet

    Set rngF = .AutoFilter.Range
    If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
                    .Cells.Count = 1 Then

          MsgBox "No records available" 'This is optional
          Exit Sub
    
    End If

End With

With rngF  'ignore the header from the count and come down one row

        Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)

End With

rngV.Next.EntireRow.Select 'use your copy code here

Sorry for never respondingto this one. My problem was due to an error elsewhere in my code, but I thank you both for your help, and to SNB for providing an alternative code to copying lines. Today was the first chance I've had to take a look at it.

Thanks,
John