OK! There are five steps in the process (payroll). A spreadsheet is
generated by our customer containing all of our jobs for the day each job has
13 columns of data including our technicians number that completed the job
and three columns of billing codes. The originals and two columns of audited
codes one column that subtracts and one that adds. My workbook has a master
sheet and a tab for each of my techs which uses an offset formula to pull
every job for that tech. The first step of the Macro imports all of the
information from the e-mail into the Master sheet and runs a text to columns
process to break every code into its own column and assigns a dollar value to
it. At this point I need a break so if there are any tech number changes
they can take place before the next step. Only tech number changes are made
because often times we are forced to close a job under a borrowed tech number
due to the wait time for a new tech to be processed into the customers
system. We change them so the right person gets paid. Once the changes are
made I need the macro to continue. It than opens only the individual tech
files that had jobs that day by comparing to our tech column on the Master
sheet. The tech files consist of sheets sunday thru saturday and a weekly
totals page that compiles quantities of the codes drawn from the daily sheets
which include an address and account number for each job and a grid that
counts the number of codes charged for each job. This page is layed out in a
format required by the customer and is printed and turned in with a copy of
the techs workorders for auditing purposes. The macro opens, updates, closes
and saves it. The third step is the weekly payroll workbook. This workbook
has a linked copy of each weekly sheet from the individual tech file and is
also the main workbook that updates every tech file with current line items,
pricing and date ranges based on a week number entered on the invoice
page.There are also three tally sheets one for weekly totals one for invoice
totals and an Employee sheet where we add other payroll items and office
personell, this sheet is than e-mailed to corporate for payroll processing.
This sheet is our main source of record keeping for every project. We have
several projects with this basic setup with many similarities and also many
differences based on the type of billing system our customer uses. Some
systems we use this to bill the customer with and other systems like the one
I'm working on now use a reverse billing system where the information is
submitted to us and we are required to parse it and submit a discrepency
report. Which brings me to my fourth step which I have not started on. I
have to use a weekly report imported from our customers website. I will use
a column from that import to search the daily master sheets from the first
step and verify that all codes were paid. The step that is missing is the
first step which uses the same macro with a couple of small changes made so
it imports from the original pre -audited code sheet and dumps the
information into the tech files and prints that day for each tech that worked
so it can be turned in with the workorders for auditing purposes. When I run
the second macro called the final it delets all the data and replaces it with
the audited data for final payroll. I could almost use the same Macro but the
customer changes the column order when they do the final and I don't want to
leave it up to my office people to reorder the columns its just easier to use
the second macro. If I ended the macro after the first step I could use the
last half on the pre-audited or final with no changes.- Hope this helps. I
really need some guidance on this one.
