Hello everyone. I have a new project that I am trying to do. As usual what starts out as a simple workbook with a few macro ideas becomes very cumbersome. This workbook is to track adjustments to payments owed to us. We want to track the data from reception, through research and into resolution.
All of the worksheets will be sorted based on the 'issue date' column (except the final sheet - completed which is sorted based on the memo # column). It would be nice if the buttons used to tell the workbook to move data ended with a sort command that sorts the data based on the above criteria.
Here is what I have.
I have a workbook (attached) which the user will data entry adjustments to checks that are coming in.
On the first sheet of the workbook:
- enter the data based on the headers given.
- In column E they will select from a list the code for the reason for the adjustment.
- Column F will describe that code.
I need VBA that will separate the data onto the appropriate sheet (each named for a code, ex: sheet 1.1). Once the data is on the correct sheet the date the information is populated needs to be entered as a static value into column G. Column F will maintain a running count of days since entry so that we can track the time it takes to resolve the problem. On the appropriate worksheet there are two columns that need to have mutually exclusive check boxes (or an equivalent way to check them to show that they have been accepted or deducted). Once the check box in either accepted or deducted has been checked (or a value such as X has been entered into either accepted or deducted) a button at the top of the sheet is depressed.
This button will then move the "accepted" issues to the accepted worksheet and the "deducted" to the deducted worksheet.
All of the coded sheets should work the same way. All of the data should be moved from the coded sheet to the accepted or deducted sheets once the research into the issue is completed and recorded by either checking the appropriate box or entering the appropriate value. If possible I would like to make the sheet so that the user can not leave the sheet with out clicking the button that moves the data to the correct sheet (accepted or deducted).
The accepted and deducted sheets work the same way. The data remains on the sheet until both the paying company and my company come to an agreement and money has changed hands. At which time the column that was not previously populated on the coded sheet (deducted was left empty on the accepted sheet and visa versa on the deducted sheet) is populated. Once a row as population in both the accepted and deducted columns a button at the top is pressed and the row is moved to the completed column.
Once on the completed column, the following data is no longer needed: entry date and the accepted and deducted columns. It would probably be easier to just not transfer these three columns from the accepted or deducted worksheets. On the completed column we need to change the sorting to be based on the memo # header. I need cell D3 on the completed sheet to keep a running count of all populated rows. I need D4 to keep a running total of all the unique memo #s (memo #s can have multiple adjustments which need to be resolved, but we are only charged one $25 fee if we accept any or all of the adjustments on a given memo). In cell D5 I need the completed sheet to keep a total of the running disputed amount for all adjustments. And finally, I need D6 to keep a running total of the fees we are charged for accepting the memo (this seems easy enough as it can be as simple as =D4*25).
It is alot I know, but if you all cannot help I will have to build a lot of macro buttons that do a lot of copy, paste, deletes, and sorts that can easily cause loss of data.
Thanks for any suggestions or solutions.
Richard
Reply With Quote
