Insurance Excel Macro / VBA

Completed Posted 2 years ago Paid on delivery
Completed Paid on delivery

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.

Excel VBA Excel Macros Excel Excel VB Capabilities

Project ID: #30637567

About the project

25 proposals Remote project Active 2 years ago

Awarded to:

(295 Reviews)
6.4

25 freelancers are bidding on average $187 for this job

sajibdigital

"Hi, I am interested in your project. I hope you will be happy after hiring me. Can we discuss more details about this project? Thanks"

$100 USD in 3 days
(66 Reviews)
5.7
mzdesmag

Hi, I’m an experienced analyst information researcher, and science scholastic author. I have complete some Ph.D. level postulation activities, including progressed factual examination of information. Scanning for remar More

$140 USD in 3 days
(7 Reviews)
4.3
bmoatar1

Hi, I have full experience with Excel, VBA ========================================================== I can carry out your project on time with high quality guarantee. I hope to discuss more in detail. Best regards. Fr More

$125 USD in 2 days
(1 Review)
0.4
osanjay777

My skill set is a perfect match for the job requirements. In particular, my editing skills and managerial experience make me an ideal candidate for the position. For example, at my last job, I managed a team of five em More

$35 USD in 2 days
(0 Reviews)
0.0
deister

Hi, there. I am Enrico from Germany. If you are looking for programmer for coding Excel VBA project., that's great for me. I have a lot of experience with Excel VBA/Macro, Visual Basic, so on. I will make a userform an More

$155 USD in 3 days
(0 Reviews)
0.0
cgeisner

I have a big knowledge in this topic an already code a softwareproject in VBA. This Task looks fairly easy to me, but i would do it cheaper, because it is my first job on this plattform.

$140 USD in 7 days
(0 Reviews)
0.0