I have been trying to teach myself macros online but have not been able to write a successful macro that does what I need.
First, the workbook has 5 total sheets.
The first 4 sheets are current data sets of employee data, existing accounts, new accounts, and sales.
Each data set is tied by the Sales Agent ID.
On the fifth, PAY, sheet I need
A button that calculates the total pay due to the SALES ID by
a. Calculating the Commission on the sales agent's existing accounts
-Finding all the records from the Exisiting Accounts sheet with the matching Sales ID and totaling the revenue and pasting into the CUrrent Revenue on the PAY Sheet
-Multiplying the Existing Account % by the Current total revenue generated by the accounts in the Mo. Commission
b. Calculating the bonus total for the number of new accounts the sales agent closed by
-finding all the records from the New Accounts sheet with the matching SALES ID and counting the number of new accounts and pasting that count number into Mo. New Acct. Count on PAY sheet
-multiplying the New Account Bonus $ amount by the number of Mo. New Acct. Count and pasting the total bonus amount due in Mo. Bonus on PAY sheet
c. Calculating the sales commission due based on the sales agent's monthly sales made
-Finding all the records from the Mo. Sales sheet with the matching sales ID and totaling the Sales Amount $ and pasting into the Sales Amount on the PAY sheet
-Multiplying the Sales % by the Sales Amount and pasting the amount into the Mo. Sales Com. on the PAY sheet
d. Calculating the total pay due to the Sales agent by
-the Sum of the Base Value + Mo. Commission Value + Mo. Bonus Value + Mo. Sales Com. value
I've attached a file of dummy test data for reference
Any help would be greatly appreciated!
Thank you!
Bookmarks