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







"Fotoman" wrote:

>
> 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.
>
>
> --
> Fotoman
> ------------------------------------------------------------------------
> Fotoman's Profile: http://www.excelforum.com/member.php...o&userid=30729
> View this thread: http://www.excelforum.com/showthread...hreadid=503958
>
>