Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Copy and Paste based on Condition | Excel Discussion (Misc queries) | |||
How do I copy a set of cells based on a logical condition | Excel Discussion (Misc queries) | |||
copy data from 1 worksheet to another based on a condition | Excel Worksheet Functions | |||
Copy Row based on a condition | Excel Worksheet Functions |