Hello,
I do not quite agree to xld's suggestion. Of course it is valid to have your data in several workbooks. And, as I understand it, having several workbooks is NOT the problem here. The problem appears to be how to structure the actions the code is supposed to do.
Hiflier, in order to figure out the steps your code is supposed to take, you do not necessarily need specific artifacts like the good ol' flowchart, although it helps. There are other ways, which, in the end, lead to the same result.
Take a few sheets of paper, and a pencil.
On the top of a sheet you write the one sentence abstraction of what the whole coding is supposed to do. As I gather from your entry above, you do have an idea of what the code is supposed to do. Actually, at this stage, do NOT think of coding but simply the business side of what you want to do. Leave enough space between the steps, a good rule is three to four fingers' width, no more than four, five steps on one sheet. In your case, it might look like this:
Mortgage loan payments and amortizations for my mobile home parks 1/2
1. One workbook for each mobile home park.
... three to four fingers' width of space ...
2. Each workbook will hold the loan payments and amortizations for the affected mobile homes in the parks in the same structure.
... three to four fingers' width of space ...
3. The calculations need to be transferred to each workbook.
... three to four fingers' width of space ...
4. The changed workbook needs to be saved.
... three to four fingers' width of space ...
5. Only one mortgage workbook for one park open while calculating.
... three to four fingers' width of space ...
Mortgage loan payments and amortizations for my mobile home parks 2/2
6. Not only the results of the calculations are to be transferred to a specific workbook, but the calculations themselves.
... three to four fingers' width of space ...
and so on.
Into the space between the points you jot down anything that comes into your mind that might support the idea.
For example, for item 1 in my suggestion, write: workbook file name = "Henderson_Park_<date>.xls" (I still use Excel 2003, therefore the *.xls)
Sheet names = Lot_nnnnn Paymnt and Lot_nnnnn Amort, where nnnnn is the identifier of the lot. Thus you would know that you need two sheets for each mobile home you own.
Continue through your above list. In the next step you bring all of the list items in an order and add supporting details. In item 2 there is the hint that all worksheets of a certain type (loan payment, amortization) have the same structure. What could that mean? I would create a template each of the loan payment and amortization sheets in the workbook that does the calculations. Next idea in item 2: Since worksheets are to be created again and again, you'll need a function that creates the worksheets and returns the handle. Think of a name, like Function CreateWorksheet(sheetType as String) As Worksheet where sheetType is e.g. "loan" or "amort" if you like.
After you're through with that, you have an idea of the flow. Re-order your processing steps, taking into account the details. Maybe you'll find that you need more steps, or that steps have common stuff. The steps get names, like "Perform Loan Payment Calculations"
Then, on a new sheet of paper, draw three columns. Title
Input Processing Output
Write down the names of the processing steps in order in the center, leave space between the lines. Left column, input, is what the step needs in order to complete e.g. sale price, down payment, loan amount, etc. Right column, output, what needs to be produced by this piece of processing, e.g. array of monthly payments.
Next is the Pseudo-Code. Take a new sheet of paper, sample title:
Detailed processing of "Perform Loan Payment Calculations"
Pseudo-Code Variables
The column "Pseudo-Code" is two thirds, the column "Variables" is one thrid of the width of the paper.
Under "Pseudo-code" you write in simple English the details of the processing, like
If loan amount is not numeric, issue error message, return to <sheet>, high-light erroneous field.
If interest rate is not numeric , issue error message, return to <sheet>, high-light erroneous field.
in the right column, jot down the variables, loan amount, interest rate, each in its respective line.
I think you get the idea. I have assumed standard data processing, that is procedural programming. It would have to be done differently if objects were in the game.
Let me know if that's a viable way for you.
Isabella