Consolidating excel files into one workbook (VBA project) - updated 9:10pm

Step 1 ? Take 1 page from each of a group of XLS files in a directory and create a workbook.? Each Tab in the new workbook will be named with the file name it was taken from (actually the last half of the file name which is a date). So if there are n files, there will be n tabs in 1 workbook. ? Step 2. Find and Replace procedures Add a column to each tab Step 3 Create summary tab ## Deliverables ? **Step 1** ? -? ? ? ? ? ? ? ? Take 1 page from each of a group of XLS files in a directory and create a single workbook.? Each Tab in the new workbook will be named with the file name it was taken from (actually the last half of the file name which is a date). -? ? ? ? ? ? ? ? files may all be in 1 directly, or in subdirectories of file.? So code should check all subdirectories.? -? ? ? ? ? ? ? ? VBA should prompt to choose folder. -? ? ? ? ? ? ? ? Most of the time the excel files will only have 1 tab, but in some cases there will be a tab labeled “cover sheet??.? I do not want this one, I want the other one, which will be named “None?? ? **Step 2** -? ? ? ? ? ? ? ? Add a column in each tab with a “unique key?? as shown in template.? Constructed from 3 columns in the following manner: o? ? ? ? ? ? 1st 6 digits from CUSIP Column (convert to all UPPER CASE) o? ? ? ? ? ? Append a “C?? on the end (e.g. 123456C) if either the Class column or Put/Call column contains “Call?? or “CALL??, else if o? ? ? ? ? ? Append a “P?? on the end (e.g. 123456P) if either the Class column or Put/Call column contains “PUT?? or “PUT??, else if o? ? ? ? ? ? Append “E??, class contains “COM??, “COMMON??, or “ORD?? (note: COM NEW, would be a positive match), else o? ? ? ? ? ? Append “X?? on the end, 123456X, if none of the above are true. o? ? ? ? ? ? **** this ‘table’ should be clearly marked in could so I can add/edit these conditionals. ? -? ? ? ? ? ? ? ? Conform all columns to the layout in template.? (They wont always be in the same place and you’ll have to search for the heading).? Data used for step 3 wont always start in the same row. ? ? Step 3 -? ? ? ? ? ? ? ? Create a Summary page that takes all the data from each of the pages synthesizes them into one file.? ? Each KEY will have a unique row, and then there will be 2 columns for each tab.? In the event that there are two datas with the same KEY in the same period, they should be added together. -? ? ? ? ? ? ? ? I’ve used vlookup in my test, but I don’t expect you to do this.? I expected all the ‘work’ was going to be done in the code.? But I really don’t care how you do it as long as the data is right. -? ? ? ? ? ? ? ? Make it so it’s possible to click on data and have it jump to the source tab.? i.e. an “on-event?? type thing, where I can either click, or hit a key combination (ctrl-shift-x) and then have it jump to source tab for that data. ? Step 4 -? ? ? ? ? ? ? ? Second summary page.? -? ? ? ? ? ? ? ? Data starts in row 15 -? ? ? ? ? ? ? ? First column should be first 6 of KEY -? ? ? ? ? ? ? ? 2nd Column Should be last digit of KEY -? ? ? ? ? ? ? ? 3rd Column Blank -? ? ? ? ? ? ? ? 4th Column first “date_Shares?? column, -? ? ? ? ? ? ? ? 5th… etc. ? ? To run file, I would like to: 1. load excel workbook that contains your VBA code 2. Worksheet will have a button to RUN.? And table for find/replace as described above.? ? ? Zip includes: Template 4? Groups of files that the macro should run on (this is not them all, just an example) After it’s completed, I will have you test VBA on a few more groups of files, which I will need your help debugging. ? ? Some known bugs that you’ll probably encounter: -? ? ? ? ? ? ? ? CUSIP numbers may contain leading 0s.? I need these.? Keep as text, so excel doesn’t cut them of. -? ? ? ? ? ? ? ? In my sample data, no result returns #NA.? please return 0 instead. ? ? ?