Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---


  #6   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy from one Sheet and paste on another sheet based on condition Prem Excel Discussion (Misc queries) 2 December 24th 07 06:05 AM
Copy and Paste based on Condition EJ Excel Discussion (Misc queries) 1 June 27th 07 11:17 PM
How do I copy a set of cells based on a logical condition getdv Excel Discussion (Misc queries) 1 October 24th 06 02:08 PM
copy data from 1 worksheet to another based on a condition [email protected] Excel Worksheet Functions 9 September 4th 06 02:45 PM
Copy Row based on a condition [email protected] Excel Worksheet Functions 1 April 19th 06 06:05 PM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"