Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Account payable

  1. #1

    Angry Account payable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi .
    i am new in excel and i try to create spreadsheet for monthly account payable. i have lots of problem i will be happy if some one could help me.
    OK i know i have to study, i did but i did not understand lots of thing.
    i could create one page for account payable and one page for account receivable and one page for account balance and to link them not to use VBA. and it works.
    but i like to take one or 100 step and create good one thanks

    1- i want the automatically each month one page create and the name of page is the date of (mm,YYYY)
    Code:
    Sub AddMonthWkst()
    'to add a new page to excel every month'
    
    
    Dim ws As Worksheet
    Dim strname As String
    Dim bcheck As Boolean
    
    
    On Error Resume Next
    strname = Format(Date, "yyyy_mm")
    bcheck = Len(Sheets(strname).Name) > 0
    
    
    If bcheck = False Then
        Set ws = Worksheets.Add(Before:=Sheets(1))
        ws.Name = strname
    this is working


    2- in new page, new table be create with the (mm,yyyy) name the same as page name or close rang like 01-2013 for page and january 2013 for table , the reason it is easy to solve after 2 years
    Code:
    If Range("A1") = "" ThenActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:D50"), , xlYes).Name = _
    "Table1" 
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStylemedium2"

    it work for one page, but all other pages when been create in other month has the same table name Table1 and create lots of problem
    i have to declare variable but i do not how to use it. to make the table the same as page name


    3- table has column of

    Code:
    Range("A1") = "Recipient"
        Range("B1") = "Check #"
        Range("C1") = "Description"
        Range("D1") = "Amount"
        Range("E1") = "Invoice Date"
        Range("F1") = "Pay Date"
        Range("G1") = "Status"
    4- the status column and other info for account payable


    Code:
    'Functioin for status of account pay able in col;umn G
        Range("G2:G50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(F2),IF(E2<$K$1,""Pay Now"",""Delay""),""paid""))"
    
    
    '.Formula for account payable cell j7
    Range("J7").Formula = "=SUMIF(Table1_1[[#All],[Status]],J$6,Table1_1[[#All],[Amount]])"
        
    
     '.Formula for account payable cell k7
        Range("K7").Formula = "=SUMIF(Table1_1[[#All],[Status]],K$6,Table1_1[[#All],[Amount]])"
        
    
     '.Formula for account payable cell l7
        Range("L7").Formula = "=SUMIF(Table1_1[[#All],[Status]],L$6,Table1_1[[#All],[Amount]])"

    work if you have to insert it in on page not for hole project, i have to declare variable and always return table 1 data result instead their table in their page result.



    5-when all monthly account payable page finish i need to make a table in account balance with 5 column name of

    1-Sheet name(date)like 01-2013,
    2-Pay (return the status of page 01-2013)
    3- pay now
    (return the status of page 01-2013)
    4- delay
    (return the status of page 01-2013)
    5- Bank transaction fee for the same month

    ** table show the total transaction in each month and has to be update automatically when i change any value in any page. (((i have problem )))
    **i use Total pay column and Bank transaction for total payable balance
    ** i use column pay now and delay to know how much money i give check and is not out from my account


    this is the code
    has lots of problem thanks

    Code:
    Sub AddMonthWkst()
    'to add a new page to excel every month'
    
    Dim ws As Worksheet
    Dim strname As String
    Dim bcheck As Boolean
    
    On Error Resume Next
    strname = Format(Date, "yyyy_mm")
    bcheck = Len(Sheets(strname).Name) > 0
    
    If bcheck = False Then
        Set ws = Worksheets.Add(Before:=Sheets(1))
        ws.Name = strname
    
    'Sub CreateTable()
    
    If Range("A1") = "" Then
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:D50"), , xlYes).Name = _
            "Table1"
     '*****when you write activesheet you have to know when you run this module and you
     'are in different sheet the part you run will insert
     'to the sheet you choose
            'No go in 2003
        ActiveSheet.ListObjects("Table1").TableStyle = "TableStylemedium2"
    
        Range("A1") = "Recipient"
        Range("B1") = "Check #"
        Range("C1") = "Description"
        Range("D1") = "Amount"
        Range("E1") = "Invoice Date"
        Range("F1") = "Pay Date"
        Range("G1") = "Status"
      Else
        
        Range("o1") = ""
     End If
    
    'End Sub
    
        Rows("1:1").RowHeight = 16
        Rows("2:50").RowHeight = 23.4
    
    'Sub ColumnWidth()
        Columns("j").ColumnWidth = 15 
        Columns("K").ColumnWidth = 15
        Columns("L").ColumnWidth = 15
        
        Columns("A").ColumnWidth = 15
        Columns("B").ColumnWidth = 8
        Columns("C").ColumnWidth = 40
        Columns("D").ColumnWidth = 13
        Columns("E").ColumnWidth = 16
        Columns("F").ColumnWidth = 16
        Columns("G").ColumnWidth = 11
        
    ' ShowDate
        Range("J1").NumberFormat = "General"
        Range("J1") = "Today Date"
        Range("J1").HorizontalAlignment = xlCenter
        Range("J1").Font.Size = "14"
        Range("J1").Font.Bold = True
        Range("J1").Font.Color = RGB(79, 129, 189)
        
        Range("k1") = Date
        Range("k1").HorizontalAlignment = xlCenter
        Range("K1").Font.Size = "14"
        Range("K1").Font.Bold = True
        Range("K1").Font.Color = RGB(79, 129, 189)
        'to display the todays date in cell k1'
    
        'Format the headers
        Range("A1:G1").Font.Bold = True
        Range("A1:G1").HorizontalAlignment = xlCenter
        Range("A1:G1").Font.ColorIndex = 2
    
        'format the column'
        Range("A2:A50").HorizontalAlignment = xlCenter
        Range("B2:B50").HorizontalAlignment = xlCenter
        Range("C2:C50").HorizontalAlignment = xlLeft
        Range("D2:D50").HorizontalAlignment = xlRight
        Range("E2:E50").HorizontalAlignment = xlCenter
        Range("F2:F50").HorizontalAlignment = xlCenter
        Range("G2:G50").HorizontalAlignment = xlCenter
    
        Range("A2:A50").NumberFormat = "General"
        Range("B2:B50").NumberFormat = ""
        Range("C2:C50").NumberFormat = "General"
        Range("D2:D50").NumberFormat = "$#,##0.00"
        Range("E2:E50").NumberFormat = "mm/dd/yyyy"
        Range("F2:F50").NumberFormat = "mm/dd/yyyy" 
        Range("G2:G50").NumberFormat = "General"
            
        Range("K5").HorizontalAlignment = xlCenter
        Range("K5") = "Account payable"
        Range("K5").Font.Bold = True
        Range("K5").Font.Color = RGB(79, 129, 189)
        Range("K5").Font.Size = "18"
    
        Range("J5:L5").Borders(xlEdgeBottom).LineStyle = xlContinuous    'add line in border
        Range("J5:L5").Borders(xlEdgeBottom).ColorIndex = 5            'add color to the border
        Range("J5:L5").Borders(xlEdgeBottom).Weight = 4                'add size of border line
    
        Range("j6") = "Paid"
        Range("J6").Font.Bold = True
        Range("J6").Font.Size = "14"
        Range("J6").HorizontalAlignment = xlCenter
        Range("J7").NumberFormat = "$#,##0.00"
    Range("J7").Formula = "=SUMIF(Table1_1[[#All],[Status]],J$6,Table1_1[[#All],[Amount]])"
    
        Range("K6") = "Pay Now"
        Range("K6").Font.Bold = True
        Range("k6").Font.Size = "14"
        Range("K6").HorizontalAlignment = xlCenter
        Range("K7").NumberFormat = "$#,##0.00"
    Range("K7").Formula = "=SUMIF(Table1_1[[#All],[Status]],K$6,Table1_1[[#All],[Amount]])"
        
        Range("L6") = "Delay"
        Range("L6").Font.Bold = True
        Range("L6").Font.Size = "14"
        Range("L6").HorizontalAlignment = xlCenter
        Range("L7").NumberFormat = "$#,##0.00"
    Range("L7").Formula = "=SUMIF(Table1_1[[#All],[Status]],L$6,Table1_1[[#All],[Amount]])"
    
    Range("G2:G50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(F2),IF(E2<$K$1,""Pay Now"",""Delay""),""paid""))"
    
        Range("K13").HorizontalAlignment = xlCenter
        Range("K13") = "Bank transaction fees"
        Range("K13").Font.Bold = True
        Range("K13").Font.Color = RGB(79, 129, 189) 'this is ok too: Range("K13").Font.ColorIndex = 5
        Range("K13").Font.Size = "18"
        
        Range("J13:L13").Borders(xlEdgeBottom).LineStyle = xlContinuous    'add line in border
        Range("J13:L13").Borders(xlEdgeBottom).ColorIndex = 5            'add color to the border
        Range("J13:L13").Borders(xlEdgeBottom).Weight = 4
        
        Range("j14") = "Date"
        Range("J14").Font.Bold = True
        Range("J14").Font.Size = "14"
        Range("J14").HorizontalAlignment = xlCenter
        Range("J15").NumberFormat = "mm/dd/yyyy"
        
        Range("K14") = "Bank's Name"
        Range("K14").Font.Bold = True
        Range("K14").Font.Size = "14"
        Range("K14").HorizontalAlignment = xlCenter
        
        Range("L14") = "Amount"
        Range("L14").Font.Bold = True
        Range("L14").Font.Size = "14"
        Range("L14").HorizontalAlignment = xlCenter
        Range("L15").NumberFormat = "$#,##0.00"
    
     End If
    
     'enter date to account balance
        Dim XDate As String
       Dim Xpay As Integer
       Dim XPay_Now As String
       Dim XDelay As String
       Dim XBank_Fee As String
       Dim Xname As String
       Dim XSum As Integer
    
        Xname = strname
    
    Worksheets(Xname).Select
    
        Xpay = "=SUM(J7)"
        XPayNow = "=SUM(K7)"
        XDelay = "=SUM(L7)"
        XBankFee = "=SUM(L15)"
    
     Worksheets("Account Balance").Select
       Worksheets("Account Balance").Range("A5").Select
       
       If Worksheets("Account Balance").Range("A5").Value <> "" Then
       ActiveCell.Offset(1, 0).Select
           Do Until ActiveCell.Value = ""
           ActiveCell.Offset(1, 0).Select
    Loop
           
        End If
    
        ActiveCell.Value =  Xname
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Xpay
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = XPayNow
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = XDelay
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = XBankFee
    
    End Sub









    Click image for larger version. 

Name:	excel.jpg 
Views:	23 
Size:	94.8 KB 
ID:	1107Click image for larger version. 

Name:	excel2.jpg 
Views:	26 
Size:	98.4 KB 
ID:	1108
    Last edited by Bob Phillips; 2013-02-01 at 05:50 PM. Reason: Added code tags

  2. #2
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Hassan, for starters, I believe each table object needs to have a unique name. Since you have already set the value of strname to name the sheet, use that same variable instead of "Table1" for the table names. That way, you will easily be able to correlate a table name with the sheet to which it belongs. Make this simple change first, then see if it solved any of your other problems.

    Greg

  3. #3
    thanks i did change the name as you said and i bring the computer time back and i create page for 12_2012 and 01_2013 and also for this month 02_2012 but when made a table he made as _12_2012, _01_2013, and _02_2013. for this reason the condition of g column (status) the formula is the same
    Range("G2:G50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(F2),IF(E2<$K$1,""Pay Now"",""Delay""),""paid""))"

    but i change the formula for J7, K7 and L7 from this ((((' Range("J7").Formula = "=SUMIF('strname'[[#All],[Status]],J$6,'strname'[[#All],[Amount]])")))) to this
    Range("J7").Formula = "=SUMIF(G:G,J$6,D)"
    Range("K7").Formula = "=SUMIF(G:G,K$6,D)"
    Range("L7").Formula = "=SUMIF(G:G,L$6,D)"

    next step
    what i do to the sum of this each of this 3 cell pulse the name of worksheet and bank transaction fee cell from each sheet enter to new table in balance sheet. with 5 column (date. pay, pay now, delay, bank fee)

    if i use .value i can not see the change or update has be done in each page for that reason i use sum= function. when ever the value change in any page automatically the value change in balance sheet. if i have to use other function i would be pleas to try it.
    thanks you mad me happy

  4. #4
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Well, I'm not sure I helped, but I'm glad you got it working.

  5. #5
    so this means no help from you for next step even any idea
    thanks hassan

  6. #6
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Quote Originally Posted by hassan1960 View Post
    so this means no help from you for next step even any idea
    thanks hassan
    No, it does not mean that. There is a bit of a language barrier, so I interpreted your response as to imply you are good to go, but obviously you are not Anyhow, you posted a lot of code above, and without a workbook as an example, it's kind of hard to follow exactly what you need done. Could you attach the file, with sample data, and the code you have as of now. Be sure to include *exactly* what you are trying to do, and why, and I'll take a look at it. You can either insert comments in cells, or better, insert text boxes and arrows so you can point out what needs to go where.

    Greg

  7. #7
    thanks how i attach the file i could see for picture and video not file.
    would you please kindly tell me how i have to do it

  8. #8
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    I've never done it, but this is a test. When you reply, look for "Go Advanced" at the bottom right hand side of the reply dialog box, and click it. Once you do, you will see the "Additional Options" section. Click on the "Manage Attachments" button. Once you do, you will see the file add file dialog. It looks a little weird, but you should see the "Add Files" button at the top right. Click it, and navigate to you file.

    Greg.
    Attached Files Attached Files

  9. #9
    thanks i did and i add note in module and inside sheets

    you will see in the module the problem is
    'enter date to account balance


    Dim Xpay As Integer
    Dim XPayNow As Integer
    Dim XDelay As Integer
    Dim XBankFee As Integer
    Dim Xname As String




    Xname = strname
    Worksheets(Xname).Select
    '==========================================================================
    'problem, i need help first of each mounth when new sheet of the mounth
    'create it for account payable i need to entre on line in table3 of account balnce
    'sheet under monthly account payable.
    'this line is very importand
    '1-by looking at this table i know which line has problem and i go to that month to fix it
    '2-i know how much check i give and not been cash
    '3- if there is problem with the date of check i will fix it
    '4- i use it to balance my bank account and my cash money
    '5- after 5 or more years by looking at this page i know where is the problem.
    '===========================
    'give data to each variable
    '****problem, problem
    Xpay = "=SUM('2013_01'!J7)" 'or XPay = "=(SUM(J7)" not good
    XPayNow = "=SUM('2013_01'!K7)"
    XDelay = "=SUM('2013_01'!L7)"
    XBankFee = "=SUM('2013_01'!L15)"

    'Xpay = Range("J7").Value ' it work but when the amount in j7 change
    ' it does not change in balance sheet on column b
    'row blong to that page
    '==================================
    '==========================================================================

    Worksheets("Account Balance").Select
    Worksheets("Account Balance").Range("A5").Select



    If Worksheets("Account Balance").Range("A5").Value <> "" Then
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
    Worksheets("Account Balance").Range("A5").End(X1Down).Select
    Loop
    Worksheets("Account Balance").Range("A5").End(X1Down).Select
    End If


    ActiveCell.Value = Xname
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Xpay
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = XPayNow
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = XDelay
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = XBankFee


    Worksheets(Xname).Select
    thanks hassan
    Attached Files Attached Files

  10. #10
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Hassan, thank you for uploading the doc. I'll just let you know I will probably not have time to look at it for a few days, as the first week of the month is always busy.

    Greg

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •