copying data from one book to another

[rvik]

hi,

i have 2 workbooks. one, wbk1, contains 50 records(say) like
a125
a237
a458
b965
b45
c115
c897
c564
c262

the second workbook ,wbk2, contains sheets named as a, b c

i want to copy records from wbk1 starting with 'a' to wbk2 sheet named
as a, similarly records starting with 'b' to wbk2 sheet named as b, and
it goes on

can it be done, pls help

thanks a lot

 

[Chuck]

If you are looking to get rid of one workbook and
spreading the data over several sheets in another, then
try the following.
In the source workbook break the a125 into two cells
(columns) say column A with have all the text values, and
Column B all the numeric values.
In the new workbook enter the formula
=IF([Book1]Sheet1!$A$1:$A$50="a",[Book1]Sheet1!
$B$1:$B$50,0)in sheet"a" and copy down the required number
of rows. Of course replace the "[Book1]Sheet1" with your
source book and sheet. Copy the formula to sheet "b" and
change the ="a" to ="b" and the same for sheet "c".
Since the data on each sheet will have blank row,
highlight all the data on a sheet and select copy, then
paste special, Values. Then sort the data.

There might be other ways but this is one that will work.

 

[Bill Helbron]

I have a slightly different version of the same question - I have my medical
records in a spreadsheet in different workbooks by year. When I open a new
workbook, say 2004, and try to copy the 2003 workbook into it to maintain the
formatting, it doesn't work. What am I doing wrong? I'm using Excel 2000.

Bill

If you are looking to get rid of one workbook and
spreading the data over several sheets in another, then
try the following.
In the source workbook break the a125 into two cells
(columns) say column A with have all the text values, and
Column B all the numeric values.
In the new workbook enter the formula
=IF([Book1]Sheet1!$A$1:$A$50="a",[Book1]Sheet1!
$B$1:$B$50,0)in sheet"a" and copy down the required number
of rows. Of course replace the "[Book1]Sheet1" with your
source book and sheet. Copy the formula to sheet "b" and
change the ="a" to ="b" and the same for sheet "c".
Since the data on each sheet will have blank row,
highlight all the data on a sheet and select copy, then
paste special, Values. Then sort the data.

There might be other ways but this is one that will work.

-----Original Message-----
hi,

i have 2 workbooks. one, wbk1, contains 50 records(say) like
a125
a237
a458
b965
b45
c115
c897
c564
c262

the second workbook ,wbk2, contains sheets named as a, b c

i want to copy records from wbk1 starting with 'a' to wbk2 sheet named
as a, similarly records starting with 'b' to wbk2 sheet named as b, and
it goes on

can it be done, pls help

thanks a lot