H
Hulk
I just started a new job (insurance broker) and one of the things that
immediately grabbed my attention is how manually intensive some of the
systems are here...when they could be automated. My degree is in
Information Systems so I have a background in technology...albeit in
Unix administration, so I would like to put that to use in my new job.
Every month, each Insurance company will send in a Commissions Report
to us. We do a lot of business with co-agents and split the commission
with them accordingly. Therefore, we are manually generating reports
for each co-agent and mailing it to them with their check.
Here is how the process exists today:
1) Receive multiple commission statements from the various insurance
company.
2) Information from all commission statements are compiled into one
Excel worksheet manually.
3) Someone manually goes through the newly compiled "Master" worksheet
and creates individual co-agent worksheets by pulling out all rows
containing the co-agent's name and pasting them into the new
worksheets.
4) Each agent's worksheet/statement is formatted bya adding their
mailing address in the top left of the worksheet as well as changing
column widths and row heights and editing certain fonts (boldface,
color). There is also a Subtotal, Adjustments, and Grand Total that
are calculated. The subtotal is just a sum of the commissions paid to
the agent for each row in his sheet (Each row represents a company that
the co-agent has worked on with us). Adjustments is a number input by
us which could be a negative (we paid too much the month before
accidentally) or a positive (we didn't pay enough in a previous month)
number. The Grand Total figure is the SUM of the Sub-Total and the
Adjustments figures.
5) The reports are mailed out to each agent.
As you can see, this is more labor intensive than needs to be and has a
potential to transpose numbers.
I have found a VBA sample code that has allowed me to separate the
applicable rows for each agent and create individual agent
spreadsheets. However, the problem I am having is that after I have
gone through and manually created the steps to format and calcualte
each individual's worksheet with the macro recorder, the results are
not the same when I run the macro.
Since the reports are identical as far as their formatting, is there a
way to format all worksheets at the same time? What I have been doing
is creating a macro for each agent's worksheet and as you can imagine
has become a very lengthy procedure.
If need be, I can email a sample of a blank agent report showing the
format needed to anyone who can help.
Thank you!
immediately grabbed my attention is how manually intensive some of the
systems are here...when they could be automated. My degree is in
Information Systems so I have a background in technology...albeit in
Unix administration, so I would like to put that to use in my new job.
Every month, each Insurance company will send in a Commissions Report
to us. We do a lot of business with co-agents and split the commission
with them accordingly. Therefore, we are manually generating reports
for each co-agent and mailing it to them with their check.
Here is how the process exists today:
1) Receive multiple commission statements from the various insurance
company.
2) Information from all commission statements are compiled into one
Excel worksheet manually.
3) Someone manually goes through the newly compiled "Master" worksheet
and creates individual co-agent worksheets by pulling out all rows
containing the co-agent's name and pasting them into the new
worksheets.
4) Each agent's worksheet/statement is formatted bya adding their
mailing address in the top left of the worksheet as well as changing
column widths and row heights and editing certain fonts (boldface,
color). There is also a Subtotal, Adjustments, and Grand Total that
are calculated. The subtotal is just a sum of the commissions paid to
the agent for each row in his sheet (Each row represents a company that
the co-agent has worked on with us). Adjustments is a number input by
us which could be a negative (we paid too much the month before
accidentally) or a positive (we didn't pay enough in a previous month)
number. The Grand Total figure is the SUM of the Sub-Total and the
Adjustments figures.
5) The reports are mailed out to each agent.
As you can see, this is more labor intensive than needs to be and has a
potential to transpose numbers.
I have found a VBA sample code that has allowed me to separate the
applicable rows for each agent and create individual agent
spreadsheets. However, the problem I am having is that after I have
gone through and manually created the steps to format and calcualte
each individual's worksheet with the macro recorder, the results are
not the same when I run the macro.
Since the reports are identical as far as their formatting, is there a
way to format all worksheets at the same time? What I have been doing
is creating a macro for each agent's worksheet and as you can imagine
has become a very lengthy procedure.
If need be, I can email a sample of a blank agent report showing the
format needed to anyone who can help.
Thank you!