Requirements for macro enabling:
Currently there are formulas in the template for valuing Col D3:D each Rec Type Tab (Transmission -> Event, not including Transmission and Audit)
1) Insert Tab where user
a. Selects prod/service
i. Values will be: Financial Wellness; Term Mailing/PruPassages; Disability Only; Disability & Absence; Voluntary; EOI
b. Enters Customer Name & Control#
2) Macro will do the following to the template to create an updated layout
a. Remove/Hide the tab or userform where user entered data
b. Remove/Hide tab ‘Revisions’
c. Update tabs (Customer Contact Details; Data Exchange Key Dates) with the Customer Name and Control#
d. Each tab (Associate – Event, not including Audit) has columns K-V with data in them (blue colored cells) These columns represent each Product chosen in 1ai and their respective comments. IE For Financial Wellness Column K refers to Financial Wellness and Column L represents comments for Financial Wellness for all applicable tabs
i. Col K, L for FW
ii. Col M, N for Term Mailing
iii. Col O, P for Disability
iv. Col Q, R for Absence
v. Col S, T for Voluntary
vi. Col U. V for EOI
e. If a product is NOT chosen in initial User form its respective columns on all tabs must be deleted. What this will do is directly update the value in D3 because of the conditional formula.
i. IE if only Financial Wellness was chosen as the product then on the Coverage tab columns M-V would be deleted. This would then update the value in D3 to be N.
ii. Keep in mind that more than 1 product could be chosen IE Financial Wellness and EOI could be chosen.
iii. Once NOT chosen products are deleted D3:D should be copied and pasted to D3:D with Values instead of formulas so they become static
f. Tabs should then be hidden based on the value for cell D3, if the cell is valued with N the tab should be hidden if it is value with anything else it should remain unhidden.
g. Update tabs (Transmissions – Event) that remain for field named ‘Client Control Number’ with the Control# entered by user in H4
h. Update Transmission (Header) Rec Type for field ‘Client Name’ with the Customer name entered by user
i. 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
iv. If all columns = O value O
j. For each Tab (Transmission – Event) that remains
i. If there is a comment valued in Columns L; N; P; R, T and/or V - move values to column called ‘Comments' (Column H)
1. If more than one column has value; add comment on separate line within the SAME cell values are being written to
2. Keep existing Data in Cells H:H, just append the new comments from L,N,P,R,T,V onto a new line in the cell
3. If all remaining Blue cells for K:K, M:M, O:O, Q:Q, S:S, U:U are N and that respective H cell has no value r data in it then value respective H cell with “Null”
a. IE Row 15 on Coverage tab, all Values for H,L,N,P,R,T,Vare N so H15 would be valued “Null”
b. KEEP IN MIND ALL PRODUCTS MAY NOT BE HERE AT THIS POINT AS YOU HAVE ALREADY DELETED PRODUCTS NOT CHOSEN…
k. Delete Columns J-V for each Rec Type tab
Macro should be created in a way that if more Products or more Rec Type Tabs Transmission – Event) are added in the future the code useful / easy enough that it can be modified to add that criteria.