Help: Need macro to copy rows to different workbooks

  • Thread starter Thread starter Einstein III
  • Start date Start date
E

Einstein III

I am very new to writting macros and really need help.

On my master worksheet I have about 100 rows with information on different
customers.

I would like to copy information for each customer to a different worksheet
unique to that customer.

Therefore, if on the master worksheet I have information on 'John,' I want
that information copied to a worksheet called 'John.'

Then, on the next row, I have information on 'Mary,' that should be copied
to worksheet called 'Mary,' etc etc.

All the help would be highly appreciated.

Thanks in advance.
 
Perhaps you'd like to try this formulas approach ..

In Sheet1
-------------
Assume the "master" table is in cols A to C
data from row2 down
(with the key column "Cust" in col A), viz:

Cust Field1 Field2
John Data1 Data1
Ben Data2 Data2
Mary Data3 Data3
John Data4 Data4
Mary Data5 Data5
John Data6 Data6
etc

List across in empty cols to the right, say F1:H1,
the Cust: John, Ben, Mary

Put in F2: =IF($A2="","",IF($A2=F$1,ROW(),""))

Copy F2 across to H2, then fill down by a safe "max"
expected number of rows of data in cols A to C,
say down to H200?

In a new sheet named: John
-----------------------------------------
Let's reserve cell A1 for the Cust name

Put in A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname into A1.
But you need to save the file first.)

Put the same col headers into A2:C2, viz.: Cust Field1 Field2

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+
64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRECT("Sheet1!$"&C
HAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64))
,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,S
heet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRE
CT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet
1!$1:$1,0)+64)),0)-1,COLUMN(A1)-1))

Note: You'd need to correct/restore the many inadvertent line wraps
/ line breaks when you copy > paste the above formula into A3

Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1's cols F:H, i.e. down to C200

You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the Cust: John, i.e. for the sample data-set above,
it'll appear as:

Cust Field1 Field2
John Data1 Data1
John Data4 Data4
John Data6 Data6

Now just duplicate/make a copy of the sheet: John,
rename it as: Ben
and you'll get the "filtered" rows for Ben:

Cust Field1 Field2
Ben Data2 Data2

Repeat the sheet duplicating and renaming
for: Mary, and you'll get:

Cust Field1 Field2
Mary Data3 Data3
Mary Data5 Data5

Adapt / extend to suit ..
 
A clarification ..

In sheet: John
--------------------
(row3 down)
Cust Field1 Field2
John Data1 Data1
John Data4 Data4
John Data6 Data6
Click to expand...

If you don't need the Cust col (in A2 down)
and wish to start from Field1 onwards,
just amend the last part of the formula in A3:

from
.... COLUMN(A1)-1))
to
.... COLUMN(B1)-1))

Copy A3 across to B3, then fill down by as many rows
as was done in Sheet1's cols F:H, i.e. down to B200

And then duplicate / rename sheet: John as before ..
 
Max

Thanks so much for your help. What took me ages to do every month, can now
be done in a few minutes.

You are a saviour. Thanks again.


Max said:
Perhaps you'd like to try this formulas approach ..

In Sheet1
-------------
Assume the "master" table is in cols A to C
data from row2 down
(with the key column "Cust" in col A), viz:

Cust Field1 Field2
John Data1 Data1
Ben Data2 Data2
Mary Data3 Data3
John Data4 Data4
Mary Data5 Data5
John Data6 Data6
etc

List across in empty cols to the right, say F1:H1,
the Cust: John, Ben, Mary

Put in F2: =IF($A2="","",IF($A2=F$1,ROW(),""))

Copy F2 across to H2, then fill down by a safe "max"
expected number of rows of data in cols A to C,
say down to H200?

In a new sheet named: John
-----------------------------------------
Let's reserve cell A1 for the Cust name

Put in A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname into A1.
But you need to save the file first.)

Put the same col headers into A2:C2, viz.: Cust Field1 Field2

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+
64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRECT("Sheet1!$"&C
HAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64))
,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,S
heet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRE
CT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet
1!$1:$1,0)+64)),0)-1,COLUMN(A1)-1))

Note: You'd need to correct/restore the many inadvertent line wraps
/ line breaks when you copy > paste the above formula into A3

Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1's cols F:H, i.e. down to C200

You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the Cust: John, i.e. for the sample data-set above,
it'll appear as:

Cust Field1 Field2
John Data1 Data1
John Data4 Data4
John Data6 Data6

Now just duplicate/make a copy of the sheet: John,
rename it as: Ben
and you'll get the "filtered" rows for Ben:

Cust Field1 Field2
Ben Data2 Data2

Repeat the sheet duplicating and renaming
for: Mary, and you'll get:

Cust Field1 Field2
Mary Data3 Data3
Mary Data5 Data5

Adapt / extend to suit ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Einstein III said:
I am very new to writting macros and really need help.

On my master worksheet I have about 100 rows with information on
different
customers.

I would like to copy information for each customer to a different worksheet
unique to that customer.

Therefore, if on the master worksheet I have information on 'John,' I
want
that information copied to a worksheet called 'John.'

Then, on the next row, I have information on 'Mary,' that should be
copied
to worksheet called 'Mary,' etc etc.

All the help would be highly appreciated.

Thanks in advance.
Click to expand...
Click to expand...
 
Max

Thanks a million for that. Works perfectly.

What took me ages to do every month, can now be done in minutes.

Thanks once again.


Max said:
Perhaps you'd like to try this formulas approach ..

In Sheet1
-------------
Assume the "master" table is in cols A to C
data from row2 down
(with the key column "Cust" in col A), viz:

Cust Field1 Field2
John Data1 Data1
Ben Data2 Data2
Mary Data3 Data3
John Data4 Data4
Mary Data5 Data5
John Data6 Data6
etc

List across in empty cols to the right, say F1:H1,
the Cust: John, Ben, Mary

Put in F2: =IF($A2="","",IF($A2=F$1,ROW(),""))

Copy F2 across to H2, then fill down by a safe "max"
expected number of rows of data in cols A to C,
say down to H200?

In a new sheet named: John
-----------------------------------------
Let's reserve cell A1 for the Cust name

Put in A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname into A1.
But you need to save the file first.)

Put the same col headers into A2:C2, viz.: Cust Field1 Field2

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+
64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRECT("Sheet1!$"&C
HAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64))
,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,S
heet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRE
CT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet
1!$1:$1,0)+64)),0)-1,COLUMN(A1)-1))

Note: You'd need to correct/restore the many inadvertent line wraps
/ line breaks when you copy > paste the above formula into A3

Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1's cols F:H, i.e. down to C200

You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the Cust: John, i.e. for the sample data-set above,
it'll appear as:

Cust Field1 Field2
John Data1 Data1
John Data4 Data4
John Data6 Data6

Now just duplicate/make a copy of the sheet: John,
rename it as: Ben
and you'll get the "filtered" rows for Ben:

Cust Field1 Field2
Ben Data2 Data2

Repeat the sheet duplicating and renaming
for: Mary, and you'll get:

Cust Field1 Field2
Mary Data3 Data3
Mary Data5 Data5

Adapt / extend to suit ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Einstein III said:
I am very new to writting macros and really need help.

On my master worksheet I have about 100 rows with information on
different
customers.

I would like to copy information for each customer to a different worksheet
unique to that customer.

Therefore, if on the master worksheet I have information on 'John,' I
want
that information copied to a worksheet called 'John.'

Then, on the next row, I have information on 'Mary,' that should be
copied
to worksheet called 'Mary,' etc etc.

All the help would be highly appreciated.

Thanks in advance.
Click to expand...
Click to expand...
 
You could use a for/each loop from a list & use
data>filter>autofilter>filter on the name>copy to the sheet desired. Record
a macro and then clean it up.
 
Don

Thanks for your suggestion, however, the solution by Max is perfect as I
only have to input the data once and it is automatically updated in the
other worksheets.

Thanks
 
Back
Top