Purpose: To allow one screen input into up to 50 different Spreadsheets.
Basically Every day money comes in to this business for up to 50 seperate distinct companies. This money amount is entered into a cell in a spreadsheet. The date of payment is also entered.
There are 50 spreadsheets- one per company.
Each company workbook has a single work sheet where this information is entered. (Tab named Indiv. Acc.) Each of these worksheet may have from 4-400 rows. Each row represents a different customer. These are all in a table and the two colums I want to write to are called payment 20xx, date paid 20xx.
Every year new columns are added to the table with these headings amended for the current year. So the table gets wider every year.
There are also lots of other columns. The important ones for this script are the last name and the residence column. All customers can be identified within each workbook by these two criteria. These are always in column A for residence number and column D for last name.
So at the moment we get say a payment from Joe Bloggs of no 7 for PQPC company. We open PQPC company spreadsheet, go to the column for 2013 payment, go down to Joe Blogg name , check that it is no 7 and enter the amount they have paid. We then go to next column and add the date paid. Then we save and close the spreadsheet.
Pretty simple until you do it 10-30 times a day!!
Oh yea all company spredsheets are in in seperate folers under one overall folder..
Structure is pretty rigid and labels on folders do not change... Overall folder-->company folder-->audit folder-->companyname.xls file.. A slight wrinkle, sometimes there may be a new company added/removed under the overall folder.
So ideally I would like to enter on a form
Customer last name-
Amount of payment-
Date of payment-
Then let the macro/script do the opening, find the customer row, find the amount paid cell, enter the amount and the date in the correct cells for that customer and rows and then close and save the file. If it finds a problem it needs to tell me!!
So there you have it.. Anyone interested