VBA, Macro, Excel

I have similar project that was done just recently. macros, pivot tables, vlookups etc. if you are good with combaining spreadsheets with different data - you will be fine. instructions: Macro: a) Open the file: - BSS by Region [login to view URL] b) Do the following steps: - add another sheet - copy values only to new sheet - add auto filter on line 9 - filter for nonblank’s in column A and copy the results to new added sheet - column P line 9 add “Group #” - column N line 9 add “ Region” - column M line 10 add “1” - column N line 10 add =A7 so ‘Carolina’ will appear in the cell - column M line 11 should contain following formula: =IF((J11<>""),M10,M10+1) - column N line 11 should contain following formula: =IF(M11=M10,N10,A10) - run these formulas all the way down as long as the data is available for all lines - add another sheet - run pivot table on count of the client based on region so you would get something like this: - - Open the file “ BSS by Broker Name [login to view URL] - add another sheet - copy values only to new sheet – name it MTD Data - row S, line 9 – name it Group # - run v lookup based on client name =VLOOKUP(A10,'[BSS by Region [login to view URL]]Sheet2'!$A$10:$Q$1452,25,FALSE) so region will be filled. - Column T, line 9 – name it Broker Name and Region - Concave Broker name and region in column T beginning in line 10 - Add broker ID in column U line 9 - Do vlookup for broker ID in column U based on Broker Name and Region from column T and data from Broker Stats [login to view URL] – spreadsheet from first assignment - =VLOOKUP(T10,'[Broker Stats [login to view URL]]Sheet2'!$P$10:$Q$1155,2,FALSE) - Here is what we should get - - In new added sheet and named BBS Qouted Accounts run pivot table on broker ID and count of brokers. Output as below: - - add new sheet and rename it “BBS Quoted Lines and Premium” - run pivot table on broker ID and “Sum of # Lines Quoted” and “Sum of Premium” columns M and N from MTD Data tab - In column E create a table that will contain following items - Column E – Broker ID, column F – Sum of # of Lines Quoted, column G – Sum of premium --- these taken from Pivot table just created. - In colum H create Bound Lines, column I – Bound Premium, J Line Hit Ratio, K, Premium Hit Ratio - Column H will be filled up with VLook up function based on broker ID from column E and spreadsheet “Bound Prem MTD [login to view URL]” - =VLOOKUP($E5,[Bound Prem MTD [login to view URL]]Bound Lines and Premium'!$B$6:$D$613,2,FALSE) --- to get Bound Lines - =VLOOKUP($E5,[Bound Prem MTD [login to view URL]]Bound Lines and Premium'!$B$6:$D$613,3,FALSE) --- to get Bound Premium - Column J is simple H/F - Column K is simple I/G - Save it. - Outlook should look like this: