need a formula to copy entire row to next worksheet

[aledger]

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?

 

[Gord Dibben]

Is it likely that more than one cell contains the string "Vice President"?

Is it likely that string would be part of a larger string in the cell(s)?


Gord Dibben Excel MVP

 

[Jef Gorbach]

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

 

[aledger]

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.

 

[aledger]

You will only see Vice President in column C throughout various rows.

 

[Gord Dibben]

aledger

Might be just as easy to Data>Autofilter for Vice President on Column C then
F5>Special>Visble Cells Only>OK

Copy to next sheet.

Anyway.......macro as requested.

Sub Copy_Rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("C1", Range("C" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If i.Value = "Vice President" Then _
i.Rows.Copy Destination:=Sheets("Sheet2") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Next i
End Sub


Gord