assistance mod code to shows balances the customers

Hi Kalil and welcome to the Forum

From the RESULT sheet in your workbook, I've seen what you want and corrected your macro (so it still operates from the button on the "account" sheet of the revised file, attached below).

Firstly, the balance formula you stated in  your question is slightly wrong, needing the correction in bold below:

Activecell.FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"

Also your macro starts with a statement which made that sheet way too big (by referring to 10,000 columns) so I changed it to Sheets("account").Range("A6:h114").ClearContents (which seems to be enough) and removed the other rows.

In the "account" sheet, I've inserted a Balance column E to your table. That means that your code has to be split to paste DATA columns A:D first, then columns E:G (to "account" F:H). I've changed the paste type to ValuesOnly (which gives a neater result in "account").

In new column E: the line under  'apply formula adds the corrected formula as far as the paste has filled values in the sheet. 

In row 6, the first balance value is copied from C3 (and a description added under H6).

REVISION 1: in the revised code extract below, I've added an If then statement on LastR to cover when date filters produce no transactions. Also added Applications.EnableEvents= False (to stop the Worksheet_Change macro triggering during execution) with ...=True at end of macro. REVISION 2: Corrected variable name in "remaining 3" copy statement and used .ShowAllData to clear Autofilter (before End If). 

Here are the changes to your code (in bold, with comments):

 LastR = Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
 If LastR > 3 Then ' copy data if there's any
    'copy first four filtered columns
    Sheets("DATA").Range("A4:D" & LastR).SpecialCells(xlCellTypeVisible).COPY
    Sheets("account").Range("A7").PasteSpecial Paste:=xlPasteValues
    ' apply formula
    Sheets("account").Range("E7:E" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=R[-1]C+RC[-2]-RC[-1]"
    'copy remaining three filtered columns
    Sheets("DATA").Range("E4:G" & LastR).SpecialCells(xlCellTypeVisible).COPY
    Sheets("account").Range("F7").PasteSpecial Paste:=xlPasteValues

    Sheets("account").Range("A5").Select
    Sheets("DATA").Range("A3:g3").ShowAllData
 End If

Otherwise your code is the same as before. I changed the formula in F3 to 

=C3+D3-E3

Change "name" then  click the "get data" button and it should work okay. If so, you can delete the sheet "RESULT" and remove the red and blue fills from column E.

Note that I added a small Worksheet_Change event macro to the "account sheet" so that the table data is cleared if the name or to/from dates in row 3 are changed (otherwise the table doesn't match the lookup values in the table, until "get data" is clicked. Here's that code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B1:C3"), Target) Is Nothing Then     
Exit Sub
End If ' Otherwise if a search value changed, clear the data
Sheets("account").Range("A6:H114").ClearContents
End Sub

Hope this is what you need.