I have a workbook with multiple sheets representing years.
Each row is the information associated with 1 publication by individuals in
our department. Column 1 contains the surnames + initials of all authors
(e.g., Smith RW, Jones MP, Johnson OK), column 2 contains the title, column 3
the journal, volume, pages.
I need to generate a list on a separate sheet that lists all of the
publications by each author for the year:
2005
Smith publications
Smith RW, Jones MP, Johnson OK, ...
Smith,...
Jones Publications
Jones MP...
Smith RW, Jones MP, Johnson OK...
As you can see, the authors may or may not be the first author listed in
column A. The list would include each row multiple times, under each coauthor.

I already have a macro that can generate a list of all of the publications
of an author for all years, but I have to manually type in the name of the
author to kick off the macro.

Thanks!
Kristin

Hi Kristen,

If you can paste your code here you will probably get the modificatio
you require!

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=50122

My current macro repeats this for each sheet:
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 = "2002"
Range("A65536").End(xlUp).Offset(1, 0).Select

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

But this pulls all of the publications for one individual for all years. I
need the publications for all individuals from one year (one sheet).

"Simon Lloyd" wrote:


Hi Kristen,

If you can paste your code here you will probably get the modification
you require!

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=501227