Macro to copy specific data from master workbook to another workbook

[Mark767]

I wonder if someone can help me out. I have a workbook that contains
multiple worksheets, one of which is a master data sheet.

I would like to copy specific data from this sheet to multiple
worksheets based on specific criteria.

Master Data Worksheet is setup as follows:

Date Salesrep Customer Project $ Status, etc.
Aug A ABC etc.
Aug A DEF etc.
Aug B GHI etc.
Aug C JKL etc.
Aug C MNO etc.
Aug D PQR etc.

I would like the macro to lookup the Salesrep name and copy the enitre
row for that rep over to the specific Salesreps workbook and then save
this data.

Thanks in advance for your help.


Mark.

 

[Bernie Deitrick]

Mark,

No need for a macro - you can use INDEX MATCH combination formulas to extract the data onto each
sheet... but do you want one row, or both rows for sales rep A?

HTH,
Bernie
MS Excel MVP

 

[Mark767]

I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.


Mark.

 

[Mark767]

I want both rows for salesrep A, etc.  In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

 

[Bernie Deitrick]

Mark,

You can extract multiple lines/and columns of data using an array formula like this (enter using
Ctrl-Shift-Enter):

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)>=ROWS($A$1:A1),INDEX('Data Sheet'!A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

copied down and over, where A1 has the rep name, and the rep names are found in column A of "Data
Sheet"

HTH,
Bernie
MS Excel MVP

I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

 

[Mark767]

One final thing, the number of rows for each sales person will change
each month so the individual salesman workbooks will somehow need to
be cleared first before the new data is copied over.



Mark.

 

[Mark767]

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)>=ROWS($A$1:A1),INDEX('Data Sheet'!
A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

Thanks for the help, unfortunately I can't get it to work. Here are
my steps.

In the new workbook for the individual salesrep, I entered this
formula and changed A1 to be the sales rep name "John".
In my actual case the sales rep names on in a workbook called "Sales
Data" on a sheet called "Master". They start below
the title information in column E6 so I changed 'Data Sheet' to be
'Master' and got a #VALUE error. Also not sure how to take
care of rep names starting in E6.


Mark.

 

[Mark767]

Bernie,

I wonder if you take another look at this. I am currently unable to
move forward.

thanks in advance.



Mark.

 

[Bernie Deitrick]

Mark,

Try this array formula (enter using Ctrl-Chift-Enter) - then copy down and over.

=IF(COUNTIF('[Sales Data.xls]Master'!$E:$E,$A$1)>=ROWS($A$1:A1),INDEX('[Sales
Data.xls]Master'!E$1:E$1000,LARGE(('[Sales Data.xls]Master'!$E$1:$E$1000=$A$1)*ROW('[Sales
Data.xls]Master'!$E$1:$E$1000),COUNTIF('[Sales Data.xls]Master'!$E:$E,$A$1)-(ROWS($B$1:B1)-1))),"")

HTH,
Bernie
MS Excel MVP

 

[Mark767]

#VALUE! could indicate you did not array-enter the formula.

CTRL + SHIFT + ENTER

Gord Dibben  MS Excel MVP

Thanks for the help. I have it working now. The problem was that I
did not have both spreadsheets in the same folder and open.


Mark.