Make multiple list from a Master Excel File 1

[lmcate2]

I have a Master Excel File that has many columns see below for layout that I received. I want to create indpendent excel files for each of the companies. For Example Hilton HOtels may have 10 lines and I want to create an Excel file with the 10 lines w/all the fields from he master file, and another Excel file for Ramada.

Hotel Name Code Address city state zip
Hilton Hotels HH 132 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....
Ramada Hotels RA 434 Main Street, City, State, Zip....
Ramada Hotels RA 434 Main Street, City, State, Zip....
Ramada Hotels RA 434 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....
Hilton Hotels HH 132 Main Street, City, State, Zip....

Thanks your all you great help.

Sam

 

[SkipVought]

Sam,

Please explain this madness!

What is the business case for this request?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lmcate2]

I hope I can clear some of the madnress:

My goal is to achieve the following.

1) Sort all the hotelbrands so they will be grouped
2) Extract each hotel brand with their comission information and create an excel file
3) Create an e-mail for each of the excel files and send it to the V.P. for review.

Thanks,

Sam

 

[SkipVought]

All kinds of ways to skin a cat.

A. You could use AutoFilter and select each Hotel, Copy and Paste Special Values on anotherr sheet, right-click the sheet tab and create a COPY of the sheet to new workbook.

B. You could, from a blank sheet, perform a parameter query using a Forms Combobox to select the hotel and declare the selected hotel cell as the trigger for the query. Data/Get External Data/New Database Query -- Excel files -- YOUR WORKBOOK -- your hotel sheet, [next],[next],[next],click the EDIT QUERY option and [finish]. In the Query grid add a CRITERIA for hotel and in the VALUE enter [?] This causes a prompt for a value. File/Return data to Excel. Before completing [OK] select Parameter -- third option -- link to a cell with the value and CHECK the Refersh Query On Change CheckBox.

There's 2 for starters.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lmcate2]

Thanks for the valuable information Skipvought.

I have tried Solution A but it is a very time cosuming process. Do you have any code where I could utilize to make Solution B

Thanks,

Sam

 

[SkipVought]

Are you using your macro recorder?

Figger out how to do something.

Macro record doing it.

Clean up the code.

Use with good health! ;-)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[lmcate2]

I am new to macros and not familiarize on how to use them, and I am doing the Auto Fiter and copy the data to an empty data sheet and save it. Then go back to master run auto fiter copy the data to another empty file and save it and go back to master.....

Thanks,

Sam

 

[SkipVought]

Well macro record all those "steps" and then get the code (alt+F11) and post back wiht the code and get some help.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Gavona]

Is this a one-off task or will you be regularly repeating it?

If the info you need could be fitted into a pivot table then I would do that. Have Hotel Name as a Page field. When you have got the table looking as you want then right click and choose "ShowPages" - you will instantly end up with one page for each Hotel Name. If you can get all the data on the table but are having difficulty getting it to look good then post back with that problem.

On the other hand..If you have loads of fields to extract and can't get it into a pivot table then I would use advanced filters and a bit of macro code.
If you name your database, criteria and output ranges then they do not all have to be on the same sheet as the database.
Advanced filter with an extract-to range of a single cell containing the text 'Hotel Name'. Extract 'unique records only' and you will have a list of all the Hotels - call this 'HotelList'.

Next record yourself using advanced filter with rangenames to extract the data for one of your hotels. Tidy up the code. Make sure it works.

Record yourself inserting a sheet,
changing the value in the criteria range (the hotel name being filtered on),
copying the column headings onto your new sheet and naming that range
running your advanced filter macro
moving your sheet to a new workbook
Tidy the code and make sure it works

Build a for-next loop to step through 'HotelList' each time running the macro you created above.


Thanks,

Gavin

 

[KenWright]

What is the information that is to the right of your example. Can you give us an example of the output you are eventually looking for? Are you looking to do any calculations on any numerical data within your tables?

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[lmcate2]

The master file contains 30 fields (unpaid commissions) Hotel Name, address, city, Confirmation no. etc. From the master file I need to sort each of the hotel Chains like Hilton, Marriott, Holiday Inn and be able to have an excel file for each of the hotel chains so I can give each of the excel file to someone and they can attach this to an e-mail to send it with a note.

Thanks guys, for all your suggetions.

Sam

 

[KenWright]

In that case I would have thought you could create a single Pivot table from your current data, get the report exactly the way you want it to appear for each file, throw the Hotel name into the page fields, use the Pivot Table 'Show pages' option to create a single sheet in identical format for every Hotel, and then if necessary have a piece of code run through and save each sheet as a separate file.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------