macro to copy and paste based on a condition
 

I need a way to copy a row of information and then paste it into a new
worksheet based on a cell containing the text "TERM". Is that possible?

thanks!

macro to copy and paste based on a condition
 

Try this
[Posted elsewhere by someone (sorry for not being able to attribute to the
original contributor)]

'This will prompt for the value you want to check for and copy the first
row, from sheet1, where Col A contains that value to Sheet2 below the data
already there..

Sub CopyRow()
Dim Answer As String
Dim LastRowOnSheet2 As Long
With Worksheets("Sheet2")
LastRowOnSheet2 = .Cells(.Rows.Count, "A").End(xlUp).Row

If LastRowOnSheet2 = 1 And .Cells(1, "A").Value = "" Then
LastRowOnSheet2 = 0
End If

Answer = InputBox("Find what in Col A in Sheet1 and copy it to Sheet2?")
Worksheets("Sheet1").Columns("A").Find(Answer).Ent ireRow. _
Copy .Range("A" & (LastRowOnSheet2 + 1))
End With
End Sub--
If you find this post helpful pl. choose "Yes"...


"kathryn462" wrote:

I need a way to copy a row of information and then paste it into a new
worksheet based on a cell containing the text "TERM". Is that possible?

thanks!

macro to copy and paste based on a condition
 

That is not exactly what I am looking for. Basically I have a list of
employees with information about them and a drop down list to retain or
terminate. I need to automatically copy all of the information onto a
separate sheet for anyone who is selected as a term.

"Sheeloo" wrote:

Try this
[Posted elsewhere by someone (sorry for not being able to attribute to the
original contributor)]

'This will prompt for the value you want to check for and copy the first
row, from sheet1, where Col A contains that value to Sheet2 below the data
already there..

Sub CopyRow()
Dim Answer As String
Dim LastRowOnSheet2 As Long
With Worksheets("Sheet2")
LastRowOnSheet2 = .Cells(.Rows.Count, "A").End(xlUp).Row

If LastRowOnSheet2 = 1 And .Cells(1, "A").Value = "" Then
LastRowOnSheet2 = 0
End If

Answer = InputBox("Find what in Col A in Sheet1 and copy it to Sheet2?")
Worksheets("Sheet1").Columns("A").Find(Answer).Ent ireRow. _
Copy .Range("A" & (LastRowOnSheet2 + 1))
End With
End Sub--
If you find this post helpful pl. choose "Yes"...


"kathryn462" wrote:

I need a way to copy a row of information and then paste it into a new
worksheet based on a cell containing the text "TERM". Is that possible?

thanks!

macro to copy and paste based on a condition
 

Go to http://www.contextures.com/excelfiles.html#Filter
and search for "Product List by Category".


--
If you find this post helpful pl. choose "Yes"...


"kathryn462" wrote:

That is not exactly what I am looking for. Basically I have a list of
employees with information about them and a drop down list to retain or
terminate. I need to automatically copy all of the information onto a
separate sheet for anyone who is selected as a term.

macro to copy and paste based on a condition
 

.. Basically I have a list of employees with information
about them and a drop down list to retain or terminate.
I need to automatically copy all of the information onto a
separate sheet for anyone who is selected as a term.

How about a simple formulas set-up which accomplishes the above?

Assume source data in Sheet1, cols A to E, data from row2 down, where the DV
statuses (eg: term) are running in B2 down

In another sheet,
The status will be input in A1, eg: term

In B2:
=IF($A$1="","",IF(Sheet1!B2=$A$1,ROW(),""))
Leave B1 empty

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1))))
Copy C2 across by as many cols as you have in Sheet1, ie to G2. Then select
B2:G2, fill down to cover the max expected extent of data in Sheet1, eg down
to row 200? Minimize/hide col B. Cols C to E will return the required results
all neatly packed at the top, and dynamic to the status which is input in A1,
and to the data entered in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---

macro to copy and paste based on a condition
 

The status will be input in A1, eg: term
The input in A1 must, of course, match exactly (except for case) with what
you actually have in the status DV col in Sheet1's col B. If you have it as
"Terminate", then input the same text in A1. And if you change the input in
A1 to: Retain, it'll return the result lines for "Retain" cases. Great
flexibility there. Instead of inputting, you could also just paste the same
status DV into A1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---