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!


--
Hulk
------------------------------------------------------------------------
Hulk's Profile: http://www.excelforum.com/member.php...o&userid=14947
View this thread: http://www.excelforum.com/showthread...hreadid=268152

You can do them in a loop

Are the sheets all in one workbook?

then you could do

for each sh in ActiveWorkbook.Worksheets
sh.Activate
MyMacro
Next

assuming MyMacro is the name of the macro that does the formatting.

You can also select the sheets and run your macro

Sheets.Select
MyMacro
Worksheets(1).Select

However, you would need to test this as it only seems to work for some
formatting options (light testing). I think I would go with the first
alternative.

--
Regards,
Tom Ogilvy


"Hulk" wrote in message
...

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!


--
Hulk
------------------------------------------------------------------------
Hulk's Profile:
http://www.excelforum.com/member.php...o&userid=14947
View this thread: http://www.excelforum.com/showthread...hreadid=268152