|
Impossible
I've been searching this group for a while, and have realised you lot are
indeed guru's, and if anybody can do this, then you can. I don't think it is possible, but then again some of the things i've read in here i didn't think were possible in excel! anyways, i wonder if someone can help me. I need to be able to pick out specific rows from one worksheet, and paste them into another worksheet (in the same file if poss). This is a cut-down version of the table (there are 12 columns and 780 rows) which a database chucks out every day. Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 I am the team leader of Rajan, Bea and Lizzie. I need to get only the data for them, and paste it into another file or worksheet. At the moment i am sorting the employee column and then scrolling down to find my team members (there are 700+ rows), selecting their data, and then pasting it into another sheet. A serious amount of cyber plutonic love will go out to whoever can solve this. Thanks very much if you try Splint |
Impossible
Create a 13th column ( I assume that the first column is A and your employee
names start in A2, so in the help column you create in row 2 (same row as the first employee) you put =OR(A2={"Rajan","Bea","Lizzie"}) so if the help column is M then you put that formula in M2 select M2 again after typing in the formula, then move the mouse cursor to the lower right corner of M and when it changes from a fat to a thin cross double click and the formula will be copied down 780 rows Now select row number one (assuming your headers are in the row above the row with the first name) by clicking number one in the row headers. Do datafilterautofilter, now you will get dropdowns for all headers, in the help column click the dropdown and select TRUE That will give you all team names you are the leader of Then press Ctrl + * (asterisk), press F5, click special and select visible cells only, copy with Ctrl + C and then go to another sheet and paste them. Takes 2 minutes at most, then remove datafilter and uncheck autofilter If the data is always the same you can record a macro while doing this and just run the macro -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Splint" <a wrote in message ... I've been searching this group for a while, and have realised you lot are indeed guru's, and if anybody can do this, then you can. I don't think it is possible, but then again some of the things i've read in here i didn't think were possible in excel! anyways, i wonder if someone can help me. I need to be able to pick out specific rows from one worksheet, and paste them into another worksheet (in the same file if poss). This is a cut-down version of the table (there are 12 columns and 780 rows) which a database chucks out every day. Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 I am the team leader of Rajan, Bea and Lizzie. I need to get only the data for them, and paste it into another file or worksheet. At the moment i am sorting the employee column and then scrolling down to find my team members (there are 700+ rows), selecting their data, and then pasting it into another sheet. A serious amount of cyber plutonic love will go out to whoever can solve this. Thanks very much if you try Splint |
Impossible
Enter the information like this, and use Data-Filter-Advanced Filter.
Choose "Filter the list, in-place". Enter $A$6:$C$16 in "List Range", and $A$1:$C$4 in "Criteria range". Click Okay. Copy the entire filtered list and paste in the next sheet. It will not paste the hidden rows. When you use the larger list you will have to expand the two ranges. Also, there is a "copy to another location" option, but you can only paste in the current worksheet. You could make "copy to" point to the bottom of the list and then cut and paste that to the new sheet. Employee WDS Order Number Rajan Lizzie Bea Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 "Splint" wrote: I've been searching this group for a while, and have realised you lot are indeed guru's, and if anybody can do this, then you can. I don't think it is possible, but then again some of the things i've read in here i didn't think were possible in excel! anyways, i wonder if someone can help me. I need to be able to pick out specific rows from one worksheet, and paste them into another worksheet (in the same file if poss). This is a cut-down version of the table (there are 12 columns and 780 rows) which a database chucks out every day. Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 I am the team leader of Rajan, Bea and Lizzie. I need to get only the data for them, and paste it into another file or worksheet. At the moment i am sorting the employee column and then scrolling down to find my team members (there are 700+ rows), selecting their data, and then pasting it into another sheet. A serious amount of cyber plutonic love will go out to whoever can solve this. Thanks very much if you try Splint |
Impossible
|
Impossible
wow, that worked! Thank You. Why doesn't it give an error when using a
macro? That seems counterintuitive. Both of the following macros worked, note one is without a name. Sub Macro1() Range("A6:C16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A1:C4"), CopyToRange:=Range("Sheet2!A1"), Unique:=False End Sub Sub Macro2() Range("A6:C16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A1:C4"), CopyToRange:=Range("Test"), Unique:=False End Sub "Don Guillett" wrote: try pasting to a defined name in another sheet using a macro but you can only paste in the current worksheet. -- Don Guillett SalesAid Software "Sloth" wrote in message ... Enter the information like this, and use Data-Filter-Advanced Filter. Choose "Filter the list, in-place". Enter $A$6:$C$16 in "List Range", and $A$1:$C$4 in "Criteria range". Click Okay. Copy the entire filtered list and paste in the next sheet. It will not paste the hidden rows. When you use the larger list you will have to expand the two ranges. Also, there is a "copy to another location" option, but you can only paste in the current worksheet. You could make "copy to" point to the bottom of the list and then cut and paste that to the new sheet. Employee WDS Order Number Rajan Lizzie Bea Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 "Splint" wrote: I've been searching this group for a while, and have realised you lot are indeed guru's, and if anybody can do this, then you can. I don't think it is possible, but then again some of the things i've read in here i didn't think were possible in excel! anyways, i wonder if someone can help me. I need to be able to pick out specific rows from one worksheet, and paste them into another worksheet (in the same file if poss). This is a cut-down version of the table (there are 12 columns and 780 rows) which a database chucks out every day. Employee WDS Order Number Mick W845324 MK659343 Mick W345325 MS603505 Rajan W547456 MP463034 Kylie W749987 ME434032 Jason W142125 MF470353 Bea W644798 MW468027 Bea W447998 MW468098 Doreen W247715 ML468023 Lizzie W849131 MU468016 Lizzie W243577 MQ468077 I am the team leader of Rajan, Bea and Lizzie. I need to get only the data for them, and paste it into another file or worksheet. At the moment i am sorting the employee column and then scrolling down to find my team members (there are 700+ rows), selecting their data, and then pasting it into another sheet. A serious amount of cyber plutonic love will go out to whoever can solve this. Thanks very much if you try Splint |
| All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com