Need Help Splitting Worksheets into Different Saved Files that all refrence a common sheet

[tjbailey10]

Ok to start out we're running Excel 2007 here at the office, but we save files under the older style .xls extention.

We run a report from our QA department on vendor performance. Currently all three purchasers have to go into the file delete sheets that don't apply to them, then save a copy of that file for each vendor, then reopen each worksheet and delete all sheets except for the common refrence sheet and the vendors report card sheet. Its a very annoying process that I'm sure a macro could solve.

So far I have this to seperate the files.

Sub MakeBooks()
  For Each sh in ThisWorkbook.Sheets
  With Activeworkbook
  .SaveAs "C:\Blah.blah"
  .close
 End With
Next
End Sub

That does a fine job of seperating each sheet into its own file, saving it, and closing it, and I can get it to save to a target directory, but there's a few problems.

1) It saves every file as "Book #" and its impossible for the buyer to know which file applies to each vendor.

2) Each sheet refrences data from another common sheet. So for example sheet "Vendor 1" refrences data from sheet "Master Shipping Data", as does sheet "Vendor 2", "Vendor 3" ect. Would there be a way to tell Excel to save individual sheets along with the "Master Shipping Data" sheet in each workbook. In other words could I have Worksheet 1 have "Master Shipping Data" and "Vendor 1", Worksheet 2 have "Master Shipping Data" and "Vendor 2" ect?

3) Is there a way to tell Excel to save each workbook by the vendors name? The Sheets in the masterwork book are labeled by their vendor so it makes them a little tough to refrence as they're called "Company A", "Company B", "Company C", instead of "Sheet 1", "Sheet 2", "Sheet 3".

This kind of complex scripting is a little over my head. Like I said I was able to get the VBS to make it seperate each sheet into its own workbook and save but unable to get it to save it with a copy of the master shipping data sheet or name the file by the vendor. Any help on this would be huge! Thanks!

 

[kpark91]

Well, as for saving each file with, you can simply take the worksheet's name by using

  With Activeworkbook
     .SaveAs "C:\" & sh.Name & ".blah"
     .close
   End With

Assuming your worksheet's name is a vendor's name, it should be fine.

 

[tjbailey10]

That fixed the naming issue. Now all I need is to figure out how to get it to save a copy of that "Master Shipping Info" sheet with each workbook. Any suggestions?

 

[kpark91]

Honestly, altho the SaveAs works really well with one worksheet but if you want to add multiple sheets to one file. You would need to create a new workbook and then add worksheets and then copy + paste the data onto your newly created workbook. I'm sure there's a better way but that's all I can think of so...

Try integrating this into your code

Sheets(sh).Copy After:=Workbooks(sh).Worksheets(Worksheets.Count)

and

Set Newbook = Workbooks.Add

 

[tjbailey10]

Thanks KPark, the newly saved files still refrence the data correctly, I'm just pretty sure once we email the workbook to the individual vendors its going to mess up since it won't be able to refrence it. I think if I have the buyers just replace the reference with the value manually it will work. I mean seriously with the time this is saving them I doubt they'll really complain about copying five two digit numbers.

 

[kpark91]

Glad to hear it works.
I'm sure there's an option to only copy + paste the values but I am unsure.
But yes, it does not seem to be worth the time to be creating a code for five two digit numbers lol.

Thanks for the feedback