Jeff,
This formula is copying the first row to the next sheet. It's also not
filtering "Vice President." I don't have Vice President listed in any cell in
the first row. Do you think there is another formula or macro for this?

Thanks for your help.

"Jef Gorbach" wrote:


"aledger" wrote in message
...
If a cell contains the word Vice President I would like to copy the entire
row it sits on to the next worksheet. Is there a way to do this as a macro
or
formula without using autofilter and copying the row manually?

Give this a try:

Sub Get_VP()
'copy column headings
Sheets("Sheet1").Rows(1).Copy Destination:=Sheets("Sheet2").Rows(1)
'find and copy Vice Presidential data rows to Sheet2
For RowIndex = 1 To ActiveSheet.UsedRange.Rows.Count
If Trim(UCase(Cells(RowIndex, 1).Value)) = "VICE PRESIDENT" Then
Sheets("Sheet1").Rows(RowIndex).Copy
Destination:=Sheets("Sheet2").Rows(RowIndex)
End If
Next RowIndex
'then eliminate the resulting blank lines between Vice President data from
above quick-n-dirty method
Sheets("Sheet2").Activate
For RowIndex = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Cells(RowIndex, 1).Value = "" Then Rows(RowIndex).Delete
Next RowIndex
End Sub