need a formula to copy entire row to next worksheet
 

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?

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

On Fri, 4 Mar 2005 13:29:02 -0800, "aledger"
wrote:

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?

"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

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

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

"Gord Dibben" wrote:

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

On Fri, 4 Mar 2005 13:29:02 -0800, "aledger"
wrote:

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?

aledger

Might be just as easy to DataAutofilter for Vice President on Column C then
F5SpecialVisble Cells OnlyOK

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

On Mon, 7 Mar 2005 10:53:09 -0800, "aledger"
wrote:

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

"Gord Dibben" wrote:

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

On Fri, 4 Mar 2005 13:29:02 -0800, "aledger"
wrote:

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?