Microsoft Query, Excel Spreadsheet modification

We have a 3rd party software program with an embedded Microsoft Query utility.  We use this utility to pull data from a Sybase database and export the data to an Excel spreadsheet.

Our company consists of 10 separate used car dealerships.  Our initial query displays data in "Sheet 1" of an Excel Workbook.  The query presents cumulative data for ALL 10 dealerships spread across several columns in the spreadsheet, such as:
Column 1 - Branch #
Column 2 - Automobile Unit #
Column 3 - Automobile Make
Column 4 - Automobile Model
Column 5 - Automobile Mileage
Column 6 - Dealership Purchase Price
Column 7 - Bank Loan Value

and on and on.

Once this spreadsheet dislplays the data for ALL dealerships on "Sheet 1", we can then use the "sort / filter" dropdown windows to display this same data for just ONE branch.  However, after the "sort / filter" occurs it redisplays the newly sorted data on the same "Sheet 1" of the Excel Workbook.

What I would like to do is continue to see the initial query results for all 10 dealerships on "Sheet 1" of the Workbook.  I would like to automate the process of "sort / filter" for Branch 1 and display that data on "Sheet 2" of the Workbook.  I would like to automate the process of "sort / filter" for Branch 2 and display that data on "Sheet 3" of the Workbook.  I would like to automate the process of "sort / filter" for Branch 3 and display that data on "Sheet 4" of the Workbook . . . and so on.

The Excel Workbook would ideally display results from the initial query on "Sheet 1" and each of the next 10 worksheets of the Workbook would display data pertinent to ONLY 1 dealership in our group as described above.

If it would help, I have a SQL statement available that correctly produces the data for all 10 dealerships in "Sheet 1" of the Excel Workbook.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
Do you have a dummy workbook for the sheet 1 only (remove sensitive data and replace with dummy data)
We will then be able to create a VBA code which will create the seperate sheets after the query is completed.
You could have multiple sheets to query the data witha WHERE clause in it for each dealership, but this would be overkill and may end up broken as dealers are added or removed (in the longe term this may happen).

A dummy file is key as the code will be built based upon the file you provide. I am certain that there will be many experts happy to provide the code once we have a starting point.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baleman2Author Commented:
Do you want me to attach the workbook only and / or the SQL statement used to produce the data?
SteveCost AccountantCommented:
Just the resulting workbook with single sheet.
This we can then split off into separate sheets for each dealership.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.