Account Payable

i am new in excel and i need help thanks.
create account payable with monthly new sheet(automatically will create the sheet)
problem is i like to add value of some cells in account balance the lines you see has problem .
i include the file and some pictures and nots i would be happy any one could help me thanks hassan


'=================================================
'enter date to account balance


Dim Xpay As Currency
Dim XPayNow As Currency
Dim XDelay As Currency
Dim XBankFee As Currency
Dim Xname As String
Dim Xfuc 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 = Range("J7") 'does not update but it record the new value any time
'XPayNow = Range("K7") ' you run the marco
'XDelay = Range("L7")
'XBankFee = Range("L15")
'--------------------------------------------
' 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 = Xfuc
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


excel1.jpgExcel2.jpg

thanks but give me lots of information which i do not need and i need one row and 5 column for each month in one table .
1- sheet name (create every month, name the same as month and year)
2- pay ( the value in J7)
3- Pay Now ( the value in K7)
4- Delay (the value in L7)
5- Bank fee (the value in L15)

may be your slotion is answer but i could not understand to modfy the code to get what i want .
please take look at the project one more time. thanks

thanks i am working on that it is not easy for me but i am trying to make it.
thanks for replay and i will use the pivot table for retrieving data of individual company for duration of months or years. and the code you give me it is complete and perfect thanks again.

i find how to insert the value of total in account payable to account balance. even when total change in account payable automatically update in account balance

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
If ActiveCell.Value = Xnme Then 'not to enter dublicate value from each page
Exit Sub
End If

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.Formula = "=SUMIF(_2013_02[Status],'2013_02'!J$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!K$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!L$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.value= Worksheets(Xname).Range("L15")



because i use the formula which located in account payable in cell j7,k7.l7 and i want this formula insert in all new account payable automatically when the sheet name change use variable(Xname) to insert formula in j7,k7,l7 of new page which create every month.
has problem of calling the worksheet name, column name and using """" or &"" or ! and all reference codes
i copy from others but i do not know what is it. but i know what i want i don't know how to write it in excel vba language

example



ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(""&Xname&""[Status],'Xname'!J$6,""&Xname&""[Amount])" ' problem with ""' &"" !"' and ........

ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!J$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
thanks

i find how to insert the value of total in account payable to account balance. even when total change in account payable automatically update in account balance

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
If ActiveCell.Value = Xnme Then 'not to enter dublicate value from each page
Exit Sub
End If

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.Formula = "=SUMIF(_2013_02[Status],'2013_02'!J$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!K$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!L$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
ActiveCell.Offset(0, 1).Select
ActiveCell.value= Worksheets(Xname).Range("L15")



because i use the formula which located in account payable in cell j7,k7.l7 and i want this formula insert in all new account payable automatically when the sheet name change use variable(Xname) to insert formula in j7,k7,l7 of new page which create every month.
has problem of calling the worksheet name, column name and using """" or &"" or ! and all reference codes
i copy from others but i do not know what is it. but i know what i want i don't know how to write it in excel vba language

example



ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=SUMIF(""&Xname&""[Status],'Xname'!J$6,""&Xname&""[Amount])" ' problem with ""' &"" !"' and ........

ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!J$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])
thanks

correct the red part and in green part is what i want and blue part is work without variable and the variable id Xname as string for current or active worksheet name


ActiveCell.Formula = "=SUMIF(""&Xname&""[Status],'Xname'!J$6,""&Xname&""[Amount])"


ActiveCell.Formula = "=SUMIF(sheet name[column name],'sheet name '!cell ,Sheet name[column name])

ActiveCell.Formula = "=SUMIF(_2013_02[Status],'2013_02'!J$6,_2013_02[Amount])" **works but when you do not use variable
'(sheet name[column name],'sheet name '!cell ,Sheet name[column name])