Using VBA to automate copying of data from master sheet to linked spreadsheets

Hi,

If you really do want to save time then you need to urgently reconsider the layout of your current Income/Expenditure sheet. Your current layout does not lend itself to easy analysis. You should be prepared to capture your data in a simple two dimensional table of data where each column represents a particular field of information, e.g.

Date
Details
Project Ref
Chq No
Income/Expenditure (i.e. each row will contain either the word "Income" or "Expenditure")
Amount
Uncleared

Once you have that in place then the next question is what are you trying to achieve. If it's the ability to analyse particular projects then arguably simply filtering the sheet will give you all you need, or maybe a Pivot Table.

Hi Sue,

The point about a database is that it records each unique record. The concept of a dedicated row for each project is the wrong way to view this. You would have a row for each occurrence of a single item of income or expenditure. Looking at my original suggestion I see I missed out the vital field (or column) for the Type of Expenditure. So the complete list of Database columns would be.

Date
Details (i.e. description)
Project Ref
Chq No
Income/Expenditure (i.e. each row will contain either the word "Income" or "Expenditure")
Expenditure Type (e.g. Salary, Staff Expenses, Telephone, Rent, Insurance
Amount
Uncleared


So for instance if you had say 5 items of expenditure and 1 item of Income for a particular project on a particular day you would create 6 data rows.

In practice you would use Data Validation drop down cells to populate columns like the Expenditure Type column which must have a specific reference. i.e. you would pick the allowable items from a list of given items. This not only avoids the need to type stuff but more importantly ensures all items of a similar nature may subsequently be totalled or analysed where necessary.

When I design systems like this, for user data entry I usually provide a single 'data entry row' above the data which the user completes. Then when all cells have been completed in this 'new data entry record' the user presses a button and a simple macro adds that record to the database and clears out the data entry cells ready for a new record.

..further to my last post I'm attaching your original workbook with an example of how the Database sheet would be populated.

I've also added a simple Pivot Table which might give you an idea of the sort of analysis you can get. But remember a Pivot Table is extremely flexible and can be altered to show different aspects in seconds by dragging and dropping the field (column labels) around in the PT. Don't think the simple example I've given is all you can do or see.

Originally Posted by suedavies

Thank you Richard, I'll give that a try, not sure if it will work for my committee as they like to see easily how much is left in each fund (project) but I might try it for a less complex group I volunteer with and see how I get on with regards to the pivot table reports - it's been a long time since I used them. I like the thought of the data validation to limit the expenditure items.

Thank you for your time

Sue

To see how much is left in each fund just introduce an opening balance record for each project in (probably) the Income category. Then the natural sum in the PT will show the balance on each project.