software

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 NumberMick 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
One way is to use datagt;autofiltergt;filter on the namegt;copy/paste. next name
the same. Record a macro while doing and then clean it up
Another is to use a helper column with a number or other identifing mark for
THAT team and sort on that and copy/paste.
Or a for/next loop.

--
Don Guillett
SalesAid Software

quot;Splintquot; lt;agt; wrote in message ...
gt; I've been searching this group for a while, and have realised you lot are
gt; indeed guru's, and if anybody can do this, then you can. I don't think it
gt; is possible, but then again some of the things i've read in here i didn't
gt; think were possible in excel!
gt;
gt; anyways, i wonder if someone can help me.
gt;
gt; I need to be able to pick out specific rows from one worksheet, and paste
gt; them into another worksheet (in the same file if poss).
gt;
gt; This is a cut-down version of the table (there are 12 columns and 780
gt; rows) which a database chucks out every day.
gt;
gt; Employee WDS Order Number
gt;
gt;
gt; Mick W845324 MK659343
gt; Mick W345325 MS603505
gt; Rajan W547456 MP463034
gt; Kylie W749987 ME434032
gt; Jason W142125 MF470353
gt; Bea W644798 MW468027
gt; Bea W447998 MW468098
gt; Doreen W247715 ML468023
gt; Lizzie W849131 MU468016
gt; Lizzie W243577 MQ468077
gt;
gt; I am the team leader of Rajan, Bea and Lizzie. I need to get only the
gt; data for them, and paste it into another file or worksheet. At the moment
gt; i am sorting the employee column and then scrolling down to find my team
gt; members (there are 700 rows), selecting their data, and then pasting it
gt; into another sheet.
gt;
gt; A serious amount of cyber plutonic love will go out to whoever can solve
gt; this.
gt;
gt; Thanks very much if you try
gt;
gt; Splint
gt;
gt;
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={quot;Rajanquot;,quot;Beaquot;,quot;Lizziequot;})

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 datagt;filtergt;autofilter, 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 datagt;filter 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

nwexcelsolutions.com

quot;Splintquot; lt;agt; wrote in message ...
gt; I've been searching this group for a while, and have realised you lot are
gt; indeed guru's, and if anybody can do this, then you can. I don't think it
gt; is possible, but then again some of the things i've read in here i didn't
gt; think were possible in excel!
gt;
gt; anyways, i wonder if someone can help me.
gt;
gt; I need to be able to pick out specific rows from one worksheet, and paste
gt; them into another worksheet (in the same file if poss).
gt;
gt; This is a cut-down version of the table (there are 12 columns and 780
gt; rows) which a database chucks out every day.
gt;
gt; Employee WDS Order Number
gt;
gt;
gt; Mick W845324 MK659343
gt; Mick W345325 MS603505
gt; Rajan W547456 MP463034
gt; Kylie W749987 ME434032
gt; Jason W142125 MF470353
gt; Bea W644798 MW468027
gt; Bea W447998 MW468098
gt; Doreen W247715 ML468023
gt; Lizzie W849131 MU468016
gt; Lizzie W243577 MQ468077
gt;
gt; I am the team leader of Rajan, Bea and Lizzie. I need to get only the
gt; data for them, and paste it into another file or worksheet. At the moment
gt; i am sorting the employee column and then scrolling down to find my team
gt; members (there are 700 rows), selecting their data, and then pasting it
gt; into another sheet.
gt;
gt; A serious amount of cyber plutonic love will go out to whoever can solve
gt; this.
gt;
gt; Thanks very much if you try
gt;
gt; Splint
gt;
gt;
Enter the information like this, and use Data-gt;Filter-gt;Advanced Filter.
Choose quot;Filter the list, in-placequot;. Enter $A$6:$C$16 in quot;List Rangequot;, and
$A$1:$C$4 in quot;Criteria rangequot;. 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
quot;copy to another locationquot; option, but you can only paste in the current
worksheet. You could make quot;copy toquot; point to the bottom of the list and then
cut and paste that to the new sheet.

EmployeeWDSOrder Number
Rajan
Lizzie
Bea

EmployeeWDSOrder Number
MickW845324MK659343
MickW345325MS603505
RajanW547456MP463034
KylieW749987ME434032
JasonW142125MF470353
BeaW644798MW468027
BeaW447998MW468098
DoreenW247715ML468023
LizzieW849131MU468016
LizzieW243577MQ468077

quot;Splintquot; wrote:

gt; I've been searching this group for a while, and have realised you lot are
gt; indeed guru's, and if anybody can do this, then you can. I don't think it
gt; is possible, but then again some of the things i've read in here i didn't
gt; think were possible in excel!
gt;
gt; anyways, i wonder if someone can help me.
gt;
gt; I need to be able to pick out specific rows from one worksheet, and paste
gt; them into another worksheet (in the same file if poss).
gt;
gt; This is a cut-down version of the table (there are 12 columns and 780 rows)
gt; which a database chucks out every day.
gt;
gt; Employee WDS Order Number
gt;
gt;
gt; Mick W845324 MK659343
gt; Mick W345325 MS603505
gt; Rajan W547456 MP463034
gt; Kylie W749987 ME434032
gt; Jason W142125 MF470353
gt; Bea W644798 MW468027
gt; Bea W447998 MW468098
gt; Doreen W247715 ML468023
gt; Lizzie W849131 MU468016
gt; Lizzie W243577 MQ468077
gt;
gt; I am the team leader of Rajan, Bea and Lizzie. I need to get only the data
gt; for them, and paste it into another file or worksheet. At the moment i am
gt; sorting the employee column and then scrolling down to find my team members
gt; (there are 700 rows), selecting their data, and then pasting it into
gt; another sheet.
gt;
gt; A serious amount of cyber plutonic love will go out to whoever can solve
gt; this.
gt;
gt; Thanks very much if you try
gt;
gt; Splint
gt;
gt;
gt;

try pasting to a defined name in another sheet using a macro
gt; but you can only paste in the current worksheet.

--
Don Guillett
SalesAid Software

quot;Slothquot; gt; wrote in message
...
gt; Enter the information like this, and use Data-gt;Filter-gt;Advanced Filter.
gt; Choose quot;Filter the list, in-placequot;. Enter $A$6:$C$16 in quot;List Rangequot;, and
gt; $A$1:$C$4 in quot;Criteria rangequot;. Click Okay. Copy the entire filtered list
gt; and paste in the next sheet. It will not paste the hidden rows. When you
gt; use the larger list you will have to expand the two ranges. Also, there
gt; is a
gt; quot;copy to another locationquot; option, but you can only paste in the current
gt; worksheet. You could make quot;copy toquot; point to the bottom of the list and
gt; then
gt; cut and paste that to the new sheet.
gt;
gt; Employee WDS Order Number
gt; Rajan
gt; Lizzie
gt; Bea
gt;
gt; Employee WDS Order Number
gt; Mick W845324 MK659343
gt; Mick W345325 MS603505
gt; Rajan W547456 MP463034
gt; Kylie W749987 ME434032
gt; Jason W142125 MF470353
gt; Bea W644798 MW468027
gt; Bea W447998 MW468098
gt; Doreen W247715 ML468023
gt; Lizzie W849131 MU468016
gt; Lizzie W243577 MQ468077
gt;
gt; quot;Splintquot; wrote:
gt;
gt;gt; I've been searching this group for a while, and have realised you lot are
gt;gt; indeed guru's, and if anybody can do this, then you can. I don't think
gt;gt; it
gt;gt; is possible, but then again some of the things i've read in here i didn't
gt;gt; think were possible in excel!
gt;gt;
gt;gt; anyways, i wonder if someone can help me.
gt;gt;
gt;gt; I need to be able to pick out specific rows from one worksheet, and paste
gt;gt; them into another worksheet (in the same file if poss).
gt;gt;
gt;gt; This is a cut-down version of the table (there are 12 columns and 780
gt;gt; rows)
gt;gt; which a database chucks out every day.
gt;gt;
gt;gt; Employee WDS Order Number
gt;gt;
gt;gt;
gt;gt; Mick W845324 MK659343
gt;gt; Mick W345325 MS603505
gt;gt; Rajan W547456 MP463034
gt;gt; Kylie W749987 ME434032
gt;gt; Jason W142125 MF470353
gt;gt; Bea W644798 MW468027
gt;gt; Bea W447998 MW468098
gt;gt; Doreen W247715 ML468023
gt;gt; Lizzie W849131 MU468016
gt;gt; Lizzie W243577 MQ468077
gt;gt;
gt;gt; I am the team leader of Rajan, Bea and Lizzie. I need to get only the
gt;gt; data
gt;gt; for them, and paste it into another file or worksheet. At the moment i
gt;gt; am
gt;gt; sorting the employee column and then scrolling down to find my team
gt;gt; members
gt;gt; (there are 700 rows), selecting their data, and then pasting it into
gt;gt; another sheet.
gt;gt;
gt;gt; A serious amount of cyber plutonic love will go out to whoever can solve
gt;gt; this.
gt;gt;
gt;gt; Thanks very much if you try
gt;gt;
gt;gt; Splint
gt;gt;
gt;gt;
gt;gt;
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(quot;A6:C16quot;).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
quot;A1:C4quot;), CopyToRange:=Range(quot;Sheet2!A1quot;), Unique:=False
End Sub

Sub Macro2()
Range(quot;A6:C16quot;).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
quot;A1:C4quot;), CopyToRange:=Range(quot;Testquot;), Unique:=False
End Subquot;Don Guillettquot; wrote:

gt; try pasting to a defined name in another sheet using a macro
gt; gt; but you can only paste in the current worksheet.
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Slothquot; gt; wrote in message
gt; ...
gt; gt; Enter the information like this, and use Data-gt;Filter-gt;Advanced Filter.
gt; gt; Choose quot;Filter the list, in-placequot;. Enter $A$6:$C$16 in quot;List Rangequot;, and
gt; gt; $A$1:$C$4 in quot;Criteria rangequot;. Click Okay. Copy the entire filtered list
gt; gt; and paste in the next sheet. It will not paste the hidden rows. When you
gt; gt; use the larger list you will have to expand the two ranges. Also, there
gt; gt; is a
gt; gt; quot;copy to another locationquot; option, but you can only paste in the current
gt; gt; worksheet. You could make quot;copy toquot; point to the bottom of the list and
gt; gt; then
gt; gt; cut and paste that to the new sheet.
gt; gt;
gt; gt; Employee WDS Order Number
gt; gt; Rajan
gt; gt; Lizzie
gt; gt; Bea
gt; gt;
gt; gt; Employee WDS Order Number
gt; gt; Mick W845324 MK659343
gt; gt; Mick W345325 MS603505
gt; gt; Rajan W547456 MP463034
gt; gt; Kylie W749987 ME434032
gt; gt; Jason W142125 MF470353
gt; gt; Bea W644798 MW468027
gt; gt; Bea W447998 MW468098
gt; gt; Doreen W247715 ML468023
gt; gt; Lizzie W849131 MU468016
gt; gt; Lizzie W243577 MQ468077
gt; gt;
gt; gt; quot;Splintquot; wrote:
gt; gt;
gt; gt;gt; I've been searching this group for a while, and have realised you lot are
gt; gt;gt; indeed guru's, and if anybody can do this, then you can. I don't think
gt; gt;gt; it
gt; gt;gt; is possible, but then again some of the things i've read in here i didn't
gt; gt;gt; think were possible in excel!
gt; gt;gt;
gt; gt;gt; anyways, i wonder if someone can help me.
gt; gt;gt;
gt; gt;gt; I need to be able to pick out specific rows from one worksheet, and paste
gt; gt;gt; them into another worksheet (in the same file if poss).
gt; gt;gt;
gt; gt;gt; This is a cut-down version of the table (there are 12 columns and 780
gt; gt;gt; rows)
gt; gt;gt; which a database chucks out every day.
gt; gt;gt;
gt; gt;gt; Employee WDS Order Number
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Mick W845324 MK659343
gt; gt;gt; Mick W345325 MS603505
gt; gt;gt; Rajan W547456 MP463034
gt; gt;gt; Kylie W749987 ME434032
gt; gt;gt; Jason W142125 MF470353
gt; gt;gt; Bea W644798 MW468027
gt; gt;gt; Bea W447998 MW468098
gt; gt;gt; Doreen W247715 ML468023
gt; gt;gt; Lizzie W849131 MU468016
gt; gt;gt; Lizzie W243577 MQ468077
gt; gt;gt;
gt; gt;gt; I am the team leader of Rajan, Bea and Lizzie. I need to get only the
gt; gt;gt; data
gt; gt;gt; for them, and paste it into another file or worksheet. At the moment i
gt; gt;gt; am
gt; gt;gt; sorting the employee column and then scrolling down to find my team
gt; gt;gt; members
gt; gt;gt; (there are 700 rows), selecting their data, and then pasting it into
gt; gt;gt; another sheet.
gt; gt;gt;
gt; gt;gt; A serious amount of cyber plutonic love will go out to whoever can solve
gt; gt;gt; this.
gt; gt;gt;
gt; gt;gt; Thanks very much if you try
gt; gt;gt;
gt; gt;gt; Splint
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;