Input Page

[Fotoman]

I'm not sure if this can be done, but I am sure that you experts will
tell me if it can't.
I handle the financial affairs for a number of clients and keep my own
records of their current bank balances on separate Excel Sheets within
the same file.
On Sheet One, I have a list of all the clients' names and I have to
enter an amount of money against each client. Sheet No. 2 and onwards
will represent a Bank Statement for each of the clients.
Am I able to enter a figure on Sheet 1 against a particular client's
name and have it automatically inputted onto their 'statement' sheet
*below* the last entry that was made?
The problem I'm coming up against is in making any subsequent entries
appear on the statement in the *_line_below_*. (i.e. getting the script
to go to the client's personal page find the last entry and drop down a
line)
Too complicated?

Please bear in mind also that I am not an expert in VB.

Many thanks.

 

[Patrick Molloy]

Quite easy, although you don't say how you associate any bank account sheet
with a client's name.
So I'll give you a scenarion that you will find simple.

You input sheet Column A will have the clients' names
column B the amounts that you want to add
column C some description
column D the relevent sheet name for tha client
row 1 will be the headings and that there may be blank rows between clients
names



We'll also assume that for each client's sheet that
column A is the date yo add the payment
column B is the description
column C is the amount
column D the total

I will assume that the total on any sheet will also be the last line used

copy the follwoing code to a standard module - you can then assign it to a
button

Option Explicit

Sub Update()
Dim client As Range
Dim ws As Worksheet
Dim sum As String
Dim newrow As Long

For Each client In
Worksheets("sheet1").Range("A:A").SpecialCells(xlCellTypeConstants)
sum = client.Offset(, 1)
'ceck there's an amount
If sum <> "" Then
If IsNumeric(sum) Then
Set ws = Worksheets(client.Offset(, 3).Value)
newrow = ws.Range("D65000").End(xlUp).Row + 1
ws.Cells(newrow, 1) = Date
ws.Cells(newrow, 2) = client.Offset(, 2) 'desc
ws.Cells(newrow, 3) = client.Offset(, 1) 'amount
ws.Cells(newrow, 4).FormulaR1C1 = "=RC3 + R[-1]C"

End If
End If

Next

msgbox "Done"
End Sub