| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring only certain rows to seperate worksheet
Is there a function in excel 2007 and 2003 for picking only certain rows /
cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi,
This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: Is there a function in excel 2007 and 2003 for picking only certain rows / cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi Mike,
Many thanks Excuse my ignorance, but what is worksheet code and how and where do I enter it. Do I put it in a cell? John "Mike H" wrote: Hi, This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: Is there a function in excel 2007 and 2003 for picking only certain rows / cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Try this:
sheet 2: Header in row 1 A2: =IF(ISERR(SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE "),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))),"" ,INDEX(INDIRECT(A$1),SMALL(IF((NAME="JD")*(PRIORIT Y="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS ($1:1)))) ctrl+shift+enter, not just enter copy across and down as far as needed "JRD" wrote: Is there a function in excel 2007 and 2003 for picking only certain rows / cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi,
On the sheet with the source data, right click the sheet tab, view code and paste the code in on the right. In the code change DestSheet to the worksheet you want the data pasted into and then run the code by pressing F5 Mike "JRD" wrote: Hi Mike, Many thanks Excuse my ignorance, but what is worksheet code and how and where do I enter it. Do I put it in a cell? John "Mike H" wrote: Hi, This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: Is there a function in excel 2007 and 2003 for picking only certain rows / cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi,
If you are looking for a non formula based approach, you may use advanced filters. Assume that the data is in range A1:D8 (including headings). In A11:B11, type name and priority. In A12:B12, type JD and Elective. Now go the next worksheet and type Name, Priority, Alive in range B4:D4. Now click on cell B6 of this sheet and go to Data Filter Advanced Filter. In the Action group, select copy to another location. in the list range, select A1:D8 of the previous sheet (where the data is). In the criteria box, select A11:B12 of the sheet where the data is. In the copy to box, highlight B4:D4 of the Output sheet. Now click on OK. Please note that this is not a dynamic solution. Everytime the base data changes, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JRD" wrote in message ... Is there a function in excel 2007 and 2003 for picking only certain rows / cells from a worksheet and copying them to a seperate worksheet in the same document e.g. A B C D NAME PRIORITY ALIVE? LAB NO 1 JD ELECTIVE Y 1 2 AN ELECTIVE N 2 3 ST URGENT Y 3 4 AN URGENT Y 1 5 JD URGENT N 2 6 JD ELECTIVE Y 3 7 ST ELECTIVE N 2 How can I pick out all the rows where column A (Name) is JD and column B (priority) is elective and then copy the data in columns A, B and C only in these rows only to another worksheet in the same document. Therefore on a seperate work sheet the following would appear for the example above: A B C NAME PRIORITY ALIVE? 1 JD ELECTIVE Y 2 JD ELECTIVE Y 3 ST URGENT Y Can excel 2003 or maybe 2007 do this? Thanks John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transferring CERTAIN Data from one worksheet to another | Excel Worksheet Functions | |||
| Transferring data from one worksheet to another | Excel Discussion (Misc queries) | |||
| How do I have seperate headers for seperate pages in 1 worksheet? | Excel Discussion (Misc queries) | |||
| Transferring cell contents from selected rows only. | Excel Worksheet Functions | |||
| Transferring data from one worksheet or workbook to another | Excel Worksheet Functions | |||
Linear Mode
