Hi there
I have attached a simple excel spreadsheet. The spreadsheet comprises a SUMMARY SHEET linked to many DETAILED SHEETS.
Generally the user populates the SUMMARY SHEET. Then creates a detailed sheet. Populates the detailed sheet. Renames it. And inserts hyperlinks.
I would like these steps to be automated via a macro. The one button macro MUST do the following:
1. The macro must create a form that requests the name, code, contact person, mobile number, office number, email address, category / component / Industry, service location
Please note - the form must have the following fields: (This will be used to populate the "Vendor Summary List" tab)
New Vendor / Supplier Information
Vendor Name Allow 50 alpha numeric characters here
Timberline Code Allow 6 alpha numeric characters here
Contact Person Allow 50 alpha numeric characters here
Mobile Number Allow 10 numeric characters here in this format #### ### ###
Office Number Allow 10 numeric characters here in this format (##) #### ####
Email address Allow 50 alpha numeric characters here
Component / Industry Allow 50 alpha numeric characters here
Service Area Location Allow 50 alpha numeric characters here
If the timberline code exists in column B of the Vendor Summary list than a "STOP" validation must come up saying, "This vendor/supplier in the list. Please search for this vendor before progressing any further."
2 - Once the form has been populated with data this data must be populated into the "Vendor Summary List" tab by the macro
3- The macro must then create a new vendor tab, in exactly the same format as the existing "Vendor Template" tab
4- The macro must populate the vendor name in cell A3 of the "New Vendor" tab
5- The macro must populate the Timbeline Code in cell A4 of the "New Vendor" tab
6- The macro must then rename the "New Vendor" tab to match the Timberline code (typed into the form above)
7- The macro must createa a hyperlink within the "Vendor Summary List" to link to the the "New Vendor" tab
8- The macro must populate the formulas in columns I, J and K, of the "Vendor Summary List"
Very Good at Excel 2003-2010 and Excel VBA Macros. Please allow me to do it. Will ensure everything is as per your requirement.
Please do not hesitate to contact me if you have any questions. I look forward to working with you