G
Guest
Workbook contains multiple sheets with papers published by various authors.
Each sheet contains papers of a different status, e.g., in press, published,
with multiple authors listed on each worksheet.
I have created the following macro to pull records from multiple sheets for
a single author. I had to set the ranges at 50 lines apart because various
authors have different numbers of records. Now I'd like to delete empty
rows.
I'm sure there is a simple addition to the macro that would delete empty rows.
Can anyone help?
Thanks,
K
Sub AllWork()
'
' AllWork Macro
' Macro recorded 9/27/2004 by Kristin Kraus
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("Published-2003").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A5"), Unique:=False
Sheets("Published-2004").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A50"),
Unique:=False
Sheets("In Press").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A100"), Unique:=False
Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A150"), Unique:=False
Sheets("In Prep").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A200"), Unique:=False
Sheets("OtherDocs").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A250"), Unique:=False
End Sub
Each sheet contains papers of a different status, e.g., in press, published,
with multiple authors listed on each worksheet.
I have created the following macro to pull records from multiple sheets for
a single author. I had to set the ranges at 50 lines apart because various
authors have different numbers of records. Now I'd like to delete empty
rows.
I'm sure there is a simple addition to the macro that would delete empty rows.
Can anyone help?
Thanks,
K
Sub AllWork()
'
' AllWork Macro
' Macro recorded 9/27/2004 by Kristin Kraus
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("Published-2003").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A5"), Unique:=False
Sheets("Published-2004").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A50"),
Unique:=False
Sheets("In Press").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A100"), Unique:=False
Sheets("Submitted").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A150"), Unique:=False
Sheets("In Prep").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A200"), Unique:=False
Sheets("OtherDocs").Cells.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange _
:=Range("A1:A2"), CopyToRange:=Range("A250"), Unique:=False
End Sub