Copying filtered range on a protected sheet

[deletedalien]

HI again,

I need to copy a certain range from a protected sheet where users CAN use filters, (so filters are not protected) but i cant unfilter the data to be able to copy the entire range i need,

and when i do copy the range indicates B2:B130 it only copies the visible cells (when filters are applied.)

Is there a way top copy all cells in the range regardless of filter selection????


here is my current code

 Dim LastRow As Long
    Sheets("Agent List").Select
    Range("A2:A10000").ClearContents
    Range("A2").Select
    Sheets("1").Range("B20:B130").Copy
    Sheets("Agent List").Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(LastRow, 1).Offset(1, 0).Select

 

[HackSlash]

You must un-filter. You could re-apply the filter when you are done, if that is what you want.

The following code is untested and might need serious debugging:

Sub RemoveRestoreFilter()
Dim ws As Worksheet, filtArr(), curFilRng As String
Set ws = ActiveSheet
With ws
    If .FilterMode Then
        With .AutoFilter
            curFilRng = .Range.Address
            With .Filters
                ReDim filtArr(1 To .Count, 1 To 3)
                For f = 1 To .Count
                    With .Item(f)
                        If .On Then
                            filtArr(f, 1) = .Criteria1
                            If .Operator Then
                                filtArr(f, 2) = .Operator
                                filtArr(f, 3) = .Criteria2
                            End If
                        End If
                    End With
                Next f
            End With
        End With
        .ShowAllData
    End If
End With
'Rest of code here
    Dim LastRow As Long
    Sheets("Agent List").Select
    Range("A2:A10000").ClearContents
    Range("A2").Select
    Sheets("1").Range("B20:B130").Copy
    Sheets("Agent List").Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(LastRow, 1).Offset(1, 0).Select
'Restore original filter
With ws
    .AutoFilterMode = False
    For col = 1 To UBound(filtArr(), 1)
        If Not IsEmpty(filtArr(col, 1)) Then
            If filtArr(col, 2) Then
                w.Range(currfilrng).AutoFilter field:=col, _
                    Criteria1:=filtArr(col, 1), _
                        Operator:=filtArr(col, 2), _
                    Criteria2:=filtArr(col, 3)
            Else
               .Range(curFilRng).AutoFilter field:=col, _
                    Criteria1:=filtArr(col, 1)
            End If
        End If
    Next col
End With
End Sub