Look for name and copy

  • Thread starter Thread starter Geert
  • Start date Start date
G

Geert

Hello

Is it possible to look in sheet1 in collum B to al te rows with the name
John en copy al these rows to sheet2 and look for the name Mike and copy
all these rows to sheet3?

In want to update by opening the file and if possible with a button on the
sheet (for if the file is already opened).

I don`t know if this is possible. I tried some things but can`t get it don
because i dont know much about VBA and formulas. Mayby there is something
like this on the internet but i can find it. Ill hope somebody can help.
 
One formulas set-up which delivers it nicely ..

Try this sample construct from my archives:
http://www.savefile.com/files/151988
AutoCopy_Data_To_Resp_Sheet_Non_Array_Approach.xls
(Full details inside, nicely rendered! Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "Colours" col, which
may contain eg: Red, Yellow, Green, etc. All lines with "Red" in the key col
will be auto-copied to the sheet named: Red, and appear neatly bunched at
the top. Ditto for lines with "Yellow", "Green", etc.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the key col value. Eg we first
formulate one child sheet for "Red", dress it up nicely, then just make
copies of the "Red" sheet, and rename these as: Yellow, Green, etc

In your instance, the "colours" above would be your names:
John, Mike and so on ..

Just adapt to suit
 
This is perfect. That is what i mean. Now i have another question. Can i
have multiple master ("parent") sheets WS1. And have individual ("child")
sheets with red, green, yellow etc. as main?? So i have one main book with
the sheets Red, Green, Yellow, etc. And have multiple WS1 sheets from
where i get the data.

And in a way that i can copy the WS1 sheet and the data from the new copy
is also copied (whithout changing to much) to the new main ("child")
sheets with red, green, yellow etc

If this is possible i want to make the files for each customer. So every
customer get his own ("child") book with this main sheet WS1 in it. And
have one ("parent") book with al the sheets with red, green, yellow etc
for the overview.

I hope this is clear and possible (od am i getting problems with updating
the data with closed workbooks?).
 
When i make a copy of the sheet red and rename it into grey it won`t work.
What do is wrong. If i rename the original sheet red into grey in won`t
work eather. Please help.
 
You need to extend the construct in the mastersheet: WS1 to cover a col for
"Grey". The sample shows WS1 having 3 cols in cols K to M for the 3 colours:
Red, Green, Yellow. With the text: Grey in N1, just copy the formula in K2*
across to N2, then fill down, and the copied (or renamed) sheet with the
name: Grey will work.
*adapted accordingly to suit your actual key col, of course
 
This is perfect. That is what i mean...
You're welcome. This closes one option here for your OP.
.. Now i have another question ...
Click to expand...
Unfortunately I'm out of suggestions here.
Hang around awhile for possible responses from others
You may wish to put in your new question as a new posting

---
 
I did like that (so it is also in the info sheet) but it won`t work. If i
rename the yellow page to grey and rename the col it works.

I ad a col N and named it Grey. copy K2 into N2 - N20. I see that he count
the rows, there is a row number in col N behind the cells with grey. Then
i made a copy of the sheet Red and placed it as last sheet. The renamed
the page into grey. Now i see the header in row1 in col a - d. The
collored ceels are there but the data grey is not filled in. What do i
wrong ?????????????????????????????????????
 
Apologies, I missed this bit of adapting earlier ..

In the child sheet (eg: in Red) we need to extend the range WS1!$K$1:$M$1 in
the formula in A2 to say: WS1!$K$1:$IV$1 (cover it to the limit - col IV),
otherwise whatever's added in cols N and above in WS1 won't be read.

In Red, replace the existing formula in A2 with:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))
Copy A2 across to D2, fill down as far as required

Then just delete the other colours sheets, and re-copy Red & rename the
copies as required. It should work fine now.
 
That works. Thanks for all the help.
Apologies, I missed this bit of adapting earlier ..

In the child sheet (eg: in Red) we need to extend the range
WS1!$K$1:$M$1 in
the formula in A2 to say: WS1!$K$1:$IV$1 (cover it to the limit - col
IV),
otherwise whatever's added in cols N and above in WS1 won't be read.

In Red, replace the existing formula in A2 with:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))
Copy A2 across to D2, fill down as far as required

Then just delete the other colours sheets, and re-copy Red & rename the
copies as required. It should work fine now.
Click to expand...
 
Back
Top