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.
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 = "2015"
Range("A65536").End(xlUp).Offset(1, 0).Select
Sheets("Published-2015").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 = "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
Bookmarks