CODEWITHSUNDEEP
excelvba
Zaid Shaikh
Zaid Shaikh

Reputation: 49

Copy filtered rows to another sheet

I want to copy the filtered row to another sheets but getting an error on below code. I have three status in column E i.e Open, Close and Overdue. I want to copy all the rows having status as open or overdue.

Error occurred at the below line

.AutoFilter Field:=5, Criteria1:="open"

Here's the code

Dim Wb As Workbook
Dim ws As Worksheet
Set Wb = Workbooks.Open(TextBox2.Text)
Set ws = Wb.Sheets(strSheetName)

With ws
    .AutoFilterMode = False
    With .Range("A1:E65536")
        .AutoFilter Field:=5, Criteria1:="open"
        .SpecialCells(xlCellTypeVisible).copy Destination:=Sheets("Master Records").Range("A1")
    End With
End With

Well, since I'm new I didn't say what was the error, nor where the input workbook where coming from... So maybe my users give me ill formatted stuff, without filter in the sheet that should be...

Upvotes: 2

Views: 9132

Answers (1)

J. Chomel
J. Chomel

Reputation: 8395

With your code, I get the usual

Run-time error '1004'

But more specifically

 AutoFilter method of Range class failed

Because there was no filter in my sheet.

So here is to add the filter for the sheet:

Sub Macro3()
  Dim Wb As Workbook
  Dim ws As Worksheet
  Set Wb = ThisWorkbook
  Set ws = Wb.Sheets("Sheet1")

  ' here is what to add:      
  Range("A1:E1").Select
  Selection.AutoFilter
  ' done

  With ws
      .AutoFilterMode = False
      With .Range("A1:E65536")
          .AutoFilter Field:=6, Criteria1:="open"
          .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Master Records").Range("A1")
...
      End With
  End With
  
End Sub

Upvotes: 1

Related Questions