Account payable

hassan1960

New member
Joined
Feb 1, 2013
Messages
17
Reaction score
0
Points
0
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










excel.jpgexcel2.jpg
 
Last edited by a moderator:
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
 
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:D)"
Range("K7").Formula = "=SUMIF(G:G,K$6,D:D)"
Range("L7").Formula = "=SUMIF(G:G,L$6,D: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
 
so this means no help from you for next step even any idea:help:
thanks hassan
 
so this means no help from you for next step even any idea:help:
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
 
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
 
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.
 

Attachments

  • Book1.xlsx
    7.7 KB · Views: 36
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
 

Attachments

  • Test333.xlsm
    51.2 KB · Views: 36
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
 
thank very much i learn a lot i fix the problem with your help and blue part is the fix code


ActiveCell.Value = Xname
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!J$6," & Xname & "!E:E)"
'(" & sheet name & "!column,'" & sheet name & "'!cell ," & Sheet name & " !column name)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!K$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!L$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUM('" & Xname & "'!L15)"

in mean time i have to validate on column in all the pages including the page will create in future i need help.

Range("F2:F50").NumberFormat = "mmmm dd,yyyy"
Range("G2:G50").NumberFormat = "mmmm dd,yyyy"
Range("H2:H50").NumberFormat = "General"


Range("H2:H50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(G2),IF(F2<TODAY(),""Pay Now"",""Delay""),Here""paid""))"
in this line i will check the date of checks with TODAY() and if Invoice Date column F smaller then today i pay now if bigger then today delay
and if the date in column G Pay Date (the date check been cash) is paid
problem if the date in G bigger then TODAY() i want the massage come and ask to correct the date. because this date from last month of bank statement can not be present or future date. the red (here) part is the part i thing we have to put the code or i have to do data validation for all the pages for column G. i do not know how and where i have to put the code
thanks
 
thank very much i learn a lot i fix the problem with your help and blue part is the fix code


ActiveCell.Value = Xname
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!J$6," & Xname & "!E:E)"
'(" & sheet name & "!column,'" & sheet name & "'!cell ," & Sheet name & " !column name)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!K$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!L$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUM('" & Xname & "'!L15)"

in mean time i have to validate on column in all the pages including the page will create in future i need help.

Range("F2:F50").NumberFormat = "mmmm dd,yyyy"
Range("G2:G50").NumberFormat = "mmmm dd,yyyy"
Range("H2:H50").NumberFormat = "General"


Range("H2:H50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(G2),IF(F2<TODAY(),""Pay Now"",""Delay""),here""paid""))"

in this line i will check the date of checks with TODAY() and if (Invoice Date) column F smaller then today i pay now if bigger then today delay
and if the we have a date in column G Pay Date (the date check been cash) is paid

problem. when date enter in G bigger then TODAY() i want the massage come and ask to correct the date. because this date from last month of bank statement can not be present or future date. the red (here) part is the part i thing we have to put the code or i have to do data validation for all the pages for column G. i do not know how and where i have to put the code

code like this
like if G2 is > TODAY() massage ("please change the date")

thanks
 
thank very much i learn a lot i fix the problem with your help and blue part is the fix code


ActiveCell.Value = Xname
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!J$6," & Xname & "!E:E)"
'(" & sheet name & "!column,'" & sheet name & "'!cell ," & Sheet name & " !column name)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!K$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!L$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUM('" & Xname & "'!L15)"

in mean time i have to validate on column in all the pages including the page will create in future i need help.

Range("F2:F50").NumberFormat = "mmmm dd,yyyy"
Range("G2:G50").NumberFormat = "mmmm dd,yyyy"
Range("H2:H50").NumberFormat = "General"


Range("H2:H50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(G2),IF(F2<TODAY(),""Pay Now"",""Delay""),here""paid""))"

in this line i will check the date of checks with TODAY() and if (Invoice Date) column F smaller then today i pay now if bigger then today delay
and if the we have a date in column G Pay Date (the date check been cash) is paid

problem. if the date in G bigger then TODAY() i want the massage come and ask to correct the date. because this date from last month of bank statement can not be present or future date. the red (here) part is the part i thing we have to put the code or i have to do data validation for all the pages for column G. i do not know how and where i have to put the code
thanks

like if G2 is > TODAY() massage ("please change the date")
 
thank very much i learn a lot i fix the problem with your help and blue part is the fix code


ActiveCell.Value = Xname
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!J$6," & Xname & "!E:E)"
'(" & sheet name & "!column,'" & sheet name & "'!cell ," & Sheet name & " !column name)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!K$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(" & Xname & "!H:H,'" & Xname & "'!L$6," & Xname & "!E:E)"

ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUM('" & Xname & "'!L15)"

in mean time i have to validate on column in all the pages including the page will create in future i need help.

Range("F2:F50").NumberFormat = "mmmm dd,yyyy"
Range("G2:G50").NumberFormat = "mmmm dd,yyyy"
Range("H2:H50").NumberFormat = "General"


Range("H2:H50").Formula = "=IF(ISBLANK(A2),"""",IF(ISBLANK(G2),IF(F2<TODAY(),""Pay Now"",""Delay""),here""paid""))"

in this line i will check the date of checks with TODAY() and if (Invoice Date) column F smaller then today i pay now if bigger then today delay
and if the we have a date in column G Pay Date (the date check been cash) is paid

problem. if the date in G bigger then TODAY() i want the massage come and ask to correct the date. because this date from last month of bank statement can not be present or future date. the red (here) part is the part i thing we have to put the code or i have to do data validation for all the pages for column G. i do not know how and where i have to put the code
thanks


like if G2 is > TODAY() massage ("please change the date")
 
sorry i did not see the page 2 for that reason i send you 4 times having a beer is better then siting too long in front of computer at lees you have good reason. sorry and thanks when ever you have time no rush.
 
Back
Top