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

 

[D_Holmes]

Worked like a charm!

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.