Filter button keeps getting deleted

[D_Holmes]

Hi All,

I am trying to run a macro that will autofilter my table and then copy the filtered data to another sheet. However, when the macro gets to a specific part of the code, I get an error message and then realize that its because the filter button under one of the filter options is deleted. When it deletes, the macro has no way of filtering the information in the column. Can someone look at my macro and let me know what I am doing wrong? Thanks.

'Paste HCH HCH PATIENT ACCESS

Next6:
Worksheets("Disaster110220").Range("A1:P1").AutoFilter Field:=14, Criteria1:="HCH HCH PATIENT ACCESS"
Worksheets("Disaster110220").Range("A1:P1").AutoFilter Field:=9, Criteria1:="ACTIVE"

    Worksheets("Disaster110220").Range("a2:p" & lr).SpecialCells(xlCellTypeVisible).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Range("a2").PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

Worksheets("Disaster110220").Range("A1:P1").AutoFilter Field:=9, Criteria1:="ONCALL", VisibleDropDown:=False

On Error GoTo Next7

    Worksheets("Disaster110220").Range("a2:p" & lr).SpecialCells(xlCellTypeVisible).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

Next7:
Worksheets("Disaster110220").Range("A1:P1").AutoFilter Field:=9, Criteria1:="LEAVE", VisibleDropDown:=False

On Error GoTo Next8

   Worksheets("Disaster110220").Range("a2:p" & lr).SpecialCells(xlCellTypeVisible).Copy (I get the error here, however the code is the same for all subsequent sections)
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

 

[Fluff]

What error message do you get?

 

[RoryA]

Your error handling is wrong. As soon as one error occurs, none of your subsequent On Error statements will work and you will simply have an unhandled error - eg if there are no visible cells.

 

[D_Holmes]

Your error handling is wrong. As soon as one error occurs, none of your subsequent On Error statements will work and you will simply have an unhandled error - eg if there are no visible cells.

Ah okay. Do you know how I can fix this?

 

[D_Holmes]

What error message do you get?

I am getting error 1004, no cells were found

 

[Fluff]

How about

With Worksheets("Disaster110220")
   .Range("A1:P1").AutoFilter Field:=14, Criteria1:="HCH HCH PATIENT ACCESS"
   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="ACTIVE"
   .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Range("a2").PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="ONCALL", VisibleDropDown:=False
    .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
   
   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="LEAVE", VisibleDropDown:=False
   .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
End With

 

[D_Holmes]

How about

With Worksheets("Disaster110220")
   .Range("A1:P1").AutoFilter Field:=14, Criteria1:="HCH HCH PATIENT ACCESS"
   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="ACTIVE"
   .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Range("a2").PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="ONCALL", VisibleDropDown:=False
    .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
  
   .Range("A1:P1").AutoFilter Field:=9, Criteria1:="LEAVE", VisibleDropDown:=False
   .AutoFilter.Range.Offset(1).Copy
    Worksheets("HCH HCH PATIENT ACCESS").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
End With

So I think its the error handling i am doing that is not correct. So, as i go through the different criteria, I am realizing that "ONCALL" AND "LEAVE" are not options for all of the fields. Which is why I need a correct error handling statement to skip over the data that is missing and proceed to the next criteria. I hope that made sense. I am having a hard time trying to articulate via messaging.

 

[Fluff]

With the code I just posted, you should not need any error handling to deal with having no filtered rows.

 

[D_Holmes]

I think that it is less to do with having filtered rows and more to do with there is just no data that matches that criteria ("HCH HCH PATIENT ACCESS" & "ONCALL" OR "LEAVE"). However, I will run this macro every month and that could change which is why I do not want to delete it out all together.

 

[Fluff]

Have you tried my suggestion?