Export Data from Excel into new workbooks

[Bumpthis]

Hi, is there anyway i can take a sheet of data in excel and then export certain rows matching a specific criteria into a new workbook?
For example i would want to take all of the A's and put them into an a workbook or a workbook template and the b's into a b workbook etc etc However the datasheet that i want this macro to run in will sometime be bigger or smaller than the previous weeks one.
ColA ColB
A 10
A 10
B 5
A 5

 

[SkipVought]

Bumpthis,

Forgive me for asking this question, but it is an important one --

Why are you chopping your data up into separate worksheets?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ETID]

A claner way is to create 2 new workbooks, A_data.xls and B_data.xls and link them to the "Master workbook"

...In A_data workbook, ...from the data menu>get external data>new data base query...when the "choose data source box" opens, deselect "use wizard box at the bottom, then from the databases tab choose excel files.
Point MSQuery to your "excel master", then from the add tables box select the "options" btn and check "system tables"

then select the sheet that has the data that you want to link to.(look for a read only check box and select it if appropriate)

when you see the "table/sheet" box in the upper frame, drag the "*" down to the the cell in the top left of the large frame.

click view>criteria and drag ColA to the 1st "Criteria field:", and type A as the Value:

goto file>return data to MSExcel


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date "master" data comes in (if you set the refresh on open option)


....repeat steps for B_data workbook, changing the criteria to B

 

[Bumpthis]

Skip, the reason is so that the person responsible for A only gets the A data and so on and so forth. I admit its not the best of things but mine is not to reason why