Error in part of macro - other identical parts work, and macro has been working for years

The error I have received is:

Run-time error 57121: application-defined or object-defined error

The portion of the code is:

Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A65536").End(xlUp).Offset(1, 0), Unique:=False

This macro pulls all of the rows with a certain piece of content off of each sheet and puts them in order on a new sheet.

The macro itself runs through multiple sheets successfully, but when it hits "Submitted" it is giving the error message.

Example of the part that runs successfully:

Range("A65536").End(xlUp).Offset(1, 0).Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "IN PRESS"
Range("A65536").End(xlUp).Offset(1, 0).Select

Sheets("In Press").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A65536").End(xlUp).Offset(1, 0), Unique:=False

Range("A65536").End(xlUp).Offset(1, 0).Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "SUBMITTED"
Range("A65536").End(xlUp).Offset(1, 0).Select

Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A65536").End(xlUp).Offset(1, 0), Unique:=False



I'm not a programmer, but someone helped me prepare this code years ago. Suddenly, today this has stopped working. Can you help me resolve the problem?

Thank you very much in advance!

I'm sorry I missed the instructions for the code tags. I'll try them here. I also apologize for not being entirely fluent in the language, but I hope I can answer your questions.

I believe that the criteria range appears on every sheet. I have a sheet for each year on which I list the papers we publish. As of today, when I run the macro, I give a name that will appear in the first column and it pulls every row from every sheet in which that name appears.

It does that successfully on sheets 2010, 2011, 2012, 2013, 2014, 2015, and "in press" but then hits sheet "submitted" and has the error.

As far as I can tell, the code is identical for each sheet, so I can't find the problem. Here is a bit more of the code.

Please Login or Register  to view this content.

I have attached a small version of the workbook. You'll see the Generator Tab, which is where I run the macro.

I go to cell A3 and type Ctrl+Shift+L to run the macro (after I put the author's name in A2. I left one in for you).

The code should be pulling all the rows with "Couldwell W" from all of the worksheets starting with "Published-2010" up to the worksheet "In prep"
but it is only working through "In press"

sample.xls

That worked for me to. I guess I should have tried that after I tried restarting the program, but I didn't think of it. I have to admit I'm glad I'm not the only one who couldn't figure out what the problem was.

Where was the HTML object on "Submitted"? I don't know where to look to remove it from my original file. Also, the picture on "Generator" - I've noticed at least one a couple times up by cell A3 and deleted it, but it seems like it is always there the next time. Is there a way to find these things that are "hidden" on my spreadsheet?

Thank you so very much for your help. I really appreciate the time you have spent helping me.