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

On Fri, 4 Mar 2005 13:29:02 -0800, "aledger"
<[email protected]> 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?

[Jef Gorbach]

"aledger" <[email protected]> wrote in message
news:[email protected]...
> 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.

"Jef Gorbach" wrote:

>
> "aledger" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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"
> <[email protected]> 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?
>
>

[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

On Mon, 7 Mar 2005 10:53:09 -0800, "aledger"
<[email protected]> 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"
>> <[email protected]> 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?
>>
>>