Bringing things out of access into excel

[jdev33]

So I posted this in the excel forum and they suggested I use access, which I know even less about than excel. Basically I have imported my spreadsheet (roughly 35 columns with around 2000 rows) into access. One column lists a numerical department code for each employee listing. I'm trying to create something, whether it be via access or excel, that scans the "department" column and pulls to another document all of the rows that correspond to a particular department. (ie. I want to have a separate spreadsheet/report for each of the 30+ departments). I need to find a way to set up each department's report/spreadsheet so that the master spreadsheet "department" column is scanned for department "12345" and each row matching that department number is pulled to the respective report/spreadsheet. Is this possible and/or easier with access, and how would I go about accomplishing this? Thanks.

Link to Excel thread: http://www.mrexcel.com/forum/showthread.php?t=577132

 

[Trevor G]

From Access you can set up a query and form and then use a DoCmd.TransferSpreadsheet command.

Create your query and then save it something like qryDepartment (Keep it open), then create a form with a combo box on it that points to the Table and only shows the department numbers once, save the form something like frmSearch and also the name the combo something like cboDepartment.

Add a command button or macro to do the transfer of the query and once you select from the combo box you click the button and it will transfer into an excel workbook that you specify.