Export to new worksheet

Hi guys,

This is the first time I have been asked to dig this far into excel, and to do what I need I will have to use VB or Macros which I am entirely new with. I have used VB a little in the past and understand the language, but never programmed for excel at all. So thank you in advance for any help you can provide me with.

Here is the situation. I have a Workbook that a client sends us semi-monthly for invoicing information. The workbook consist of a cover page worksheet which is a breakdown of the past half month 14-16 days, which is what the remainder of the worksheets in the workbook consist of.

This workbook is actually an invoice for all the properties we have inspected, but the problem is we pay them off to each inspector, where the customer sends it in simply by the property and associated information, i.e. work order number, owner, address etc.

What I need to do is have a way to search the entire workbook, not just one work sheet, by the zip code. I want to sort by the zip codes which is column "Q" and then somehow cross reference it with the inspector's associated to that zip code. This is where my problem lies. I have inspector A and he is assigned zip code 1,2,3,4, and 5 I want to be able to have those zip codes associated with that inspector even though that information is not included in the workbook. If I have to manually add that information into the workbook that is fine.

Than once the workbook is sorted by inspector I would like to export to individual spreadsheets for each inspector showing every property they have inspected for that period.

I have found some macros to sort and export that seem like they might work for my needs. My main issue is with adding the association with inspector to the zip codes because that information is not there. So any pointer into tying these processes together would be greatly appreciated.

Thanks,
Dave

Alright that worked Great for adding the names!

What option can I use to have that vlookup go through the other worksheets in the workbook? I was dabbling around with the indirect() option as I heard this is what I would need, but no luck as of yet.

I think the biggest issue is that it's a workbook. All the sheets 1-14, 1-15, or 1-16 depending on the month, can all be on one worksheet. That is how I tested you vlookup method by placing everyday on one larger sheet.

So maybe instead of trying to dig into a workbook I will just combine all the worksheets first, add the lookup, and then split them based on contractor.

What is the best way to combine a workbook into one worksheet?

Waht is th ebest way to break one worksheet into multiple seperate sheets by value "contractor name" ?

Alright Well to make it simple I would like to first combine all the worksheets that are the same.

The workbook consist of 17 sheets max. The first page is for the monthly summary pages and the rest are as follows
"They are semi-monthly so each workbook is half the months report"
Sheets 2-15 "28 days february "
Sheets 2-16 "any 30 day month" or
Sheets2-17 "any month with 31 days"

These sheets are all the exact same format having colums ranging from A-V

I placed the Vlookup in column W and the reference table is in Y & Z

So before I even add the Vlookup I would like to combine these common sheets into one sheet.

Than add the vlookup to the one large sheet

Than split the sheet into seperate sheets based on the new output from vlookup in coloumn W "Inspector"

And thank you so much for your help. I have learned more about the capabilities of excel over the past week than I ever thought possible.

I can place the refference table anywhere on this workbook. All I need is the raw data included in the worksheet, it does not need to be pretty. lol

Destroying the old work book to re-sort it into the new "inspector" layout is not an issue at all i can always make a duplicate of the original before hand. Whatever method is easiest.

You exactly right I want to use the vlookup results to sort all the inspectors into individual worksheets. It does not matter if the workseets are exported as individual files, or if we just make a new workbook containing all the inspector workseets. Again whatever is simplest to do.

I do not know if we need the Trash worksheet. currently its a very specific coverage area we service and if we do not have a assigned inspector for a zip code, then we label that zip code as NEW VOLUME and I associate that zip code with vlookup that way. But you never know when someone will toss something in, and I like th eidea of the trash sheet just in case there is data not on the lookup.


If i understand you right we will be putting the vlookup refference table in the first sheet "summary sheet" and than apply the vlookup to all the other worksheets in the workbook.

Next we will need to create a new workbook or seperate work sheets using the new inspector data.

Sounds exactly what I need to do.

No problem. Do you want the one with the vlookup added to the worksheet? Or just the original i get from the client without anything added?

Where would you like me to send it to?

yes each worksheet contains the same header row.

where would you like me to send it to?

Here you go. The Zip/Inspector list is on the summary page, as well as the vlookup i was running.

That worked Perfect!!! That is exactly what I needed.
I had to break the links as well but the new workbook is more than enough for what we will be using it for.

You are a life saver. Thank you very much.

Issue definitely Solved, I will be going over this through and through until I understand every bit of the code. Not as bad as I thought with excel just have to get use to the language.

Again you have been a great help.