Use Macro to create excel scorecards out of excel workbooks

[Positive]

Hi,

I have 100 financial statements in 100 seperate excel workbooks, how
do I pick and choose some data that exist in those workbooks, import
them into 100 different scorecards ( excel workbooks) and make
calculation such as financial ratios in those scorecards.

Eg

Book 1 ( Financial Statements); I have hundreds of rows of expenses
and others, but I only want to pick out the total expenses of admin,
total insurance expense, total payroll expenses, and bad debts which
will appear in scorecard 1 (excel workbook). In the scorecard 1, I
want to put some formulas to calculate financial ratios such as debt
coverage ratio, operating income per unit of apartment...

I have about 100 properties in 100 workbooks that needs calculated
monthly. How do I automate this?

Many thanks
Lan

 

[Guest]

This would take some time to do. I think I'd approach it this way though:

1) Where are the 100 financial statement workbooks stored. If they are all
in one directory/folder with nothing else OR if other files are there, but
these have a standard naming convention, I'd first select the folder and loop
through the directory looking at the files of interest.


2) What sheet in the workbook is the data stored in? If I know I'm going
to be extracting data from a bunch of similar files, I try to define the
worksheet codenames for the files before they are populated. Since this
probably isn't the case, we'll need to know how to find the worksheet with
the data on it.

3) Where on the worksheets is the information you want to calculate?

You may want to split up your questions and get pieces of this working.

HTH,
Barb Reinhardt