Results 1 to 2 of 2

Thread: create VBA modular for Monthly account payable

  1. #1

    Angry create VBA modular for Monthly 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 WorksheetDim strname As StringDim bcheck As BooleanOn Error Resume Nextstrname = Format(Date, "yyyy_mm")bcheck = Len(Sheets(strname).Name) > 0If 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("A150"), , 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 WorksheetDim 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") = "" ThenActiveSheet.ListObjects.Add(xlSrcRange, Range("A150"), , 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("D250").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("D250").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 = strnameWorksheets(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).SelectLoop 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 = XBankFeeEnd SubClick image for larger version. 

Name:	excel.jpg 
Views:	20 
Size:	94.8 KB 
ID:	1109Click image for larger version. 

Name:	excel2.jpg 
Views:	23 
Size:	98.4 KB 
ID:	1110


    Last edited by hassan1960; 2013-02-01 at 06:46 PM.

  2. #2
    [QUOTE=hassan1960;6490]hi .


    this is the code
    has lots of problem thanks

    Code:

    Sub AddMonthWkst()
    'to add a new page to excel every month'

    Dim ws As WorksheetDim 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

    'CreateTable

    If Range("A1") = "" Then
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A150"), , xlYes).Name = _ "Table1"



    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

    Rows("1:1").RowHeight = 16
    Rows("2:50").RowHeight = 23.4

    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("D250").HorizontalAlignment = xlRight
    Range("E2:E50").HorizontalAlignment = xlCenter
    Range("F2:F50").HorizontalAlignment = xlCente
    r Range("G2:G50").HorizontalAlignment = xlCenter
    Range("A2:A50").NumberFormat = "General"
    Range("B2:B50").NumberFormat = ""
    Range("C2:C50").NumberFormat = "General"
    Range("D250").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)
    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 = strnameWorksheets(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 = XBankFeeEnd Sub



Posting Permissions

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