Insurance Excel Macro

Requirements for macro enabling: Currently there are formulas in the template for valuing Col D & Col H on each Rec Type Tab

Insert Tab where user
Selects prod/service
i. Values will be: Financial Wellness; Term Mailing/PruPassages; Disability Only; Disability & Absence; Voluntary; EOI

Enters Customer Name & Control#


Macro will do the following to the template to create an updated layout
Remove/Hide the tab where user entered data
Remove/Hide tab ‘Revisions’
Update tabs (Customer Contact Details; Data Exchange Key Dates) with the Customer Name and Control#
Remove/Hide tabs for Record Types that are NOT required for the Prod/Service selected
Update Record Types that remain for field named ‘Client Control Number’ with the Control# entered by user
Update Transmission (Header) Rec Type for field ‘Client Name’ with the Customer name entered by user
Update Audit (Trailer) Rec Type
i. If a record type is NOT required default Column D to N and Comments (Column H) to Null

ii. If a rec type is required update Column D to Y

iii. If a rec type if R Conditionally update Column D to R Conditionally

For each Rec Type tab that remains
i. Based on value in Cols K; M; O; Q; S; U - value the column called ‘Required (Y/N/R Conditionally/O)' (Column D) with the proper value based on the following rules

If all Columns = N - value N - AND - value Comments for row with ‘Null’
If at least one column = Y - value Y
If any column NE ‘Y’ and at least one = R=Conditionally - value R=Conditionally
If all columns = O value O
ii. If there is a comment valued in Columns L; N; P; R, T and/or V - move values to column called ‘Comments' (Column H)

If more than one column has value; start new comment on separate line within the cell values are being written to
Delete Columns J-V for each Rec Type tab