create automatically monthly account payable and account balance

H

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 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("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 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("A1:D50"), , 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("D2:D50").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("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)
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

excel.jpgexcel2.jpg
 
You must log in or register to reply here.
Back
Top