Invoice.xlsm How to get customer database and payment due list

Mukund Vyas · Mar 23, 2014

Hello

i have created a xlsm file for making invoice bill. which includes sr. no. party name, date, amount, particulars, vat and total amount.
i want to make a new sheet which includes... a list of all customers and their details like sr. no of bill, date and amount. and i want to add payment date and due payment list. in due sheet.
i have two sheets for making bills, call charges and tax invoice, from that two sheets i want to make a new sheet for list of customers with amount details.
i am attaching the file here to understand easily
Please provide help for which formula to use and how to use.

thanks
Mukund Vyas

vijay.vizzu · Mar 24, 2014

Dear Mukund....

I think in your sheet, Format not standard, i mean to say item description range was differ in some of the sheets.

If you are using standard format, then below code will work for you

Code:

 Sub ExtractDetails()
  Dim ws As Worksheet
  Dim cell As Range
  Dim i As Integer
  Dim PartyName As String
  Dim RefNo As String
  Dim RefDt As Date
  Dim RefAmt As Integer
  Dim wsh As Worksheet
  Dim ResWs As Worksheet
 
  Set ws = Sheets("Call Charges") 'change sheet name if required
  Set ResWs = Worksheets.Add
 
  ws.Select
  i = 2
  For Each cell In Range("B9:K500")
  If cell.Value = "To," Then
  PartyName = cell.Offset(0, 1).Value
  RefDt = cell.Offset(0, 8).Value
  RefNo = cell.Offset(1, 8).Value
  RefAmt = cell.Offset(33, 8).Value
  ResWs.Activate
  Range("A" & i).Value = PartyName
  Range("b" & i).Value = RefNo
  Range("C" & i).Value = RefDt
  Range("D" & i).Value = RefAmt
  i = i + 1
  ws.Activate
  End If
  Next
 
End Sub

vijay.vizzu · Mar 24, 2014

Sorry to all excel moderators, i don't know how to paste the code in this forum

Somendra Misra · Mar 24, 2014

Hi Vijay,

I edited your post. You can paste your code by clicking the button shown below.

Regards,

vijay.vizzu · Mar 24, 2014

Thank you somendra... that means whenever i want to paste the code, i need to type code(vb) and paste the code

Is that right?

Somendra Misra · Mar 24, 2014

Follow the tip mentioned above your post and don't put & try to put in CAPS CODE.

Regards,

Mukund Vyas · Mar 24, 2014

vijay.vizzu said:

Dear Mukund....

I think in your sheet, Format not standard, i mean to say item description range was differ in some of the sheets.

If you are using standard format, then below code will work for you

Code:

 Sub ExtractDetails()
  Dim ws As Worksheet
  Dim cell As Range
  Dim i As Integer
  Dim PartyName As String
  Dim RefNo As String
  Dim RefDt As Date
  Dim RefAmt As Integer
  Dim wsh As Worksheet
  Dim ResWs As Worksheet

  Set ws = Sheets("Call Charges") 'change sheet name if required
  Set ResWs = Worksheets.Add

  ws.Select
  i = 2
  For Each cell In Range("B9:K500")
  If cell.Value = "To," Then
  PartyName = cell.Offset(0, 1).Value
  RefDt = cell.Offset(0, 8).Value
  RefNo = cell.Offset(1, 8).Value
  RefAmt = cell.Offset(33, 8).Value
  ResWs.Activate
  Range("A" & i).Value = PartyName
  Range("b" & i).Value = RefNo
  Range("C" & i).Value = RefDt
  Range("D" & i).Value = RefAmt
  i = i + 1
  ws.Activate
  End If
  Next

End Sub

Dear Vijay

Thanks for reply...

Did u understand what i want actually... I want a list of all customers in other sheet, then i can see total billing amount and check payment due...
do u have standard invoice file...

SirJB7 · Mar 24, 2014

vijay.vizzu said:
Sorry to all excel moderators, i don't know how to paste the code in this forum

@vijay.vizzu
Hi!
Or by just following the guidelines on the frame just above the reply text box field.
Regards!

Mukund Vyas · Mar 24, 2014

PLEASE HELP GUYS....

vijay.vizzu · Mar 24, 2014

Dear Mukund Vyas

Please find below code, kindly copy and paste it in a module. I hope it will resolve your problem

Code:

Private Sub ExtractDetails()
    Dim ws As Worksheet
    Dim cell As Range
    Dim i As Integer
    Dim PartyName As String
    Dim RefNo As String
    Dim RefDt As Date
    Dim RefAmt As Integer
    Dim wsh As Worksheet
    Dim ResWs As Worksheet
   
    Set ws = Sheets("Call Charges")
    Set wsh = Sheets("TAX Invoice")
    Set ResWs = Worksheets.Add
   
    Application.ScreenUpdating = False
    ws.Select
    i = 2
    For Each cell In Range("B9:K500")
        If cell.Value = "To," Then
            PartyName = cell.Offset(0, 1).Value
            RefDt = cell.Offset(0, 8).Value
            RefNo = cell.Offset(1, 8).Value
            RefAmt = cell.Offset(33, 8).Value
            ResWs.Activate
            Range("A" & i).Value = PartyName
            Range("b" & i).Value = RefNo
            Range("C" & i).Value = RefDt
            Range("D" & i).Value = RefAmt
            i = i + 1
            ws.Activate
        End If
    Next
   
    wsh.Select
   
    For Each cell In Range("B10:K500")
        If cell.Value = "To," Then
            PartyName = cell.Offset(0, 1).Value
            RefDt = cell.Offset(0, 8).Value
            RefNo = cell.Offset(1, 8).Value
            RefAmt = cell.Offset(32, 8).Value
            ResWs.Activate
            lastRow = Range("A500").End(xlUp).Row + 1
            Range("A" & lastRow).Value = PartyName
            Range("b" & lastRow).Value = RefNo
            Range("C" & lastRow).Value = RefDt
            Range("D" & lastRow).Value = RefAmt
            lastRow = lastRow + 1
            wsh.Activate
        End If
    Next
    Application.ScreenUpdating = True
End Sub

SirJB7 · Mar 24, 2014

Mukund Vyas said:
PLEASE HELP GUYS....

Hi, Mukund Vyas!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, 23 minutes!!!... absolutely unbearable... but you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?

Mukund Vyas · Mar 25, 2014

SirJB7 said:
Hi, Mukund Vyas!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, 23 minutes!!!... absolutely unbearable... but you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?

It seems that your are angry...

I am not in hurry.. If you feel like that then sorry for that...

Dear Vijay..

i have copied that code and in alt=f11 pasted that code..
but how to use that code, i m not getting..

bobhc · Mar 25, 2014

Good day Mukund Vyas

Sorry to all excel moderators, i don't know how to paste the code in this forum

Then why do you not take the time to read this...it is at the top of the forum section you are using.

http://chandoo.org/forum/threads/help-where-do-i-put-the-code.12112/

You say that it appears SirJB7 is angry...not at all, you are not reading the posts or do not understand what is being said, you posted at 08:05 and then at 08:28 just 23 minutes later you post

PLEASE HELP GUYS....

....(ALL CAPS IS SHOUTING AND FROWNED ON BY THE FORUM MEMBERS)

Please read:-

http://chandoo.org/forum/threads/new-users-please-read.294/

As you seem to be struggling to understand posts I will repeat.....

This forum operates 24/7 with members in countries all around the world, a question may be answered by someone and then go quite, there are many reasons for this as members have many things to do outside of the Forum. Eat, sleep, work, hobbies, family the list goes on and on. A reply and answer may be urgent and important to you, but not to the members.

If you are in such a rush and need an answer NOW pay a consultant.

Members give their time and advice free and due to their Excel skills this forum has very few unanswered questions, all they ask in not to be treated as a free resource to do others work.

.

Mukund Vyas · Mar 25, 2014

Dear bobhc,
I am totaly agree with you..
You all, take time to reply.. whenever i am free i do reply...i have no problem..take time.. thanks you all for replying... whenever i get mail i check and reply.. it so u people thinks that i m free to chat here.. some misunderstanding....

bobhc · Mar 25, 2014

Did you paste the code into the VBA editor or into a module as Vijay Kumar said you should do?

Mukund Vyas · Mar 25, 2014

Yes sir.. I hv pasted it.. but how to use this

bobhc · Mar 25, 2014

But where have you pasted the code in the editor or a module, have you read the link I posted about where and how to put the code

Mukund Vyas · Mar 25, 2014

By pressing Alt+f11 Paste

bobhc · Mar 25, 2014

And then you select paste? That is the VBA editor not a module. Read the link I posted, you will receive more help from members if you show you are trying.

Mukund Vyas · Mar 25, 2014

Ohk... I will check module and paste the code
Thnx

Mukund Vyas · Mar 25, 2014

yes now i have pasted the code in to the module1.. what to do next where to check its effect.

bobhc · Mar 25, 2014

Here we are on post number 22 and you have finally done what Vijay. voice asked you to do with post number 10.

Why not use a button to do as you want by calling the module.

You could upload your latest workbook.

.

Mukund Vyas · Mar 25, 2014

plz help me by checking workbook..

vijay.vizzu · Mar 26, 2014

Dear Mukund..

As i said earlier that, your format is not in standard (ranges and cell address) so i just modified the format by deleted blank rows. I am uploading the file for your reference. I created a button labeled as "Extract details", whenever you click that button, a new sheet will be added with the details from both sheets.

Hope will resolve the issue.

Mukund Vyas · Mar 26, 2014

vijay.vizzu said:
Dear Mukund..

As i said earlier that, your format is not in standard (ranges and cell address) so i just modified the format by deleted blank rows. I am uploading the file for your reference. I created a button labeled as "Extract details", whenever you click that button, a new sheet will be added with the details from both sheets.

Hope will resolve the issue.

Dear Vijay....

Thanks a lot.. your are themaster of excel....

excellent...work... thanks for giving time for me for such a wonderful result...

now i want one more thing to do..
when i create a new bill, when i enter party name, i want drop down list there to select party name from a sheet which will include all party names..

and i can use pivot table there to select from drop down list to get total amount of a particular party, how to do it?

thanks

Invoice.xlsm How to get customer database and payment due list

Member

Attachments

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Rōnin

Member

Member

Excel Rōnin

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Attachments

Member

Attachments

Member