dcsimg
Results 1 to 10 of 10

Thread: Macro for tab wise invoice creation

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    55

    Macro for tab wise invoice creation


    Hi

    I want to develop a macro to create an invoice as per the template for every tab.


    Name:  vessel.jpg
Views: 188
Size:  43.7 KB


    Please help...ill attached excel hereVDA.zip
    Last edited by dinukamp; Oct 5th, 2018 at 05:09 AM.

  2. #2

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    55

    Re: Macro for tab wise invoice creation

    please help

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,778

    Re: Macro for tab wise invoice creation

    i tested this it does what you ask, but i noticed some other things you may still need to do, like update dates, or check if there is any value to invoice (as in the first sheet), also the tos number (cell i2) for the above image does not match the tos numbers available in the other worksheets, which only have a value in d2

    first i created an invoice template worksheet (named invoice) for which i copied the sample invoice and deleted all the values to be updated
    Code:
    Sub mkinvoice()
    Dim inv As Worksheet, tmp As Worksheet, t As Worksheet
    Set inv = Sheets("invoice")
    For Each t In ThisWorkbook.Worksheets
        If Not t.Name = "invoice" Then
            inv.Copy , t
            Set tmp = Sheets(t.Index + 1)
            tmp.Name = t.Name & "_INV"
            tmp.Cells(15, 6) = Replace(tmp.Cells(15, 6), "  ", " " & t.Name & " ")
            tmp.Cells(17, 2) = t.Name
            tmp.Cells(23, 3) = t.Cells(2, 4)
            tmp.Cells(24, 3) = t.Cells(2, 4)
            tmp.Cells(24, 5) = t.Cells(25, 10)
            
        End If
    Next
    End Sub
    the code worked as i intended, without error, positioned each invoice after the relevant data sheet, if it is not as you expected then i misunderstood
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    55

    Re: Macro for tab wise invoice creation

    Name:  Capture.jpg
Views: 54
Size:  31.1 KB

    getting a error message

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,534

    Re: Macro for tab wise invoice creation

    It's hard to read error messages in images. What is the error, and on which line?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,778

    Re: Macro for tab wise invoice creation

    first i created an invoice template worksheet (named invoice)
    the error s that there is no sheet named invoice
    it is almost impossible to read the image

    getting a error message
    is not an adequate description for any problem

    i should have also told you that for F15 i left 2 spaces in the content DFRCL AC, to populate the centre part, though in hindsight it would have been just as easy to have the cell empty and concatenate the whole string in the code
    as in some places i was just guessing as to the desired result you will have to fix some parts
    Last edited by westconn1; Oct 5th, 2018 at 04:53 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    55

    Re: Macro for tab wise invoice creation

    It worked when i rename the sheet as invoice



    How do we fix this problem....can you help me with the below

    Quote Originally Posted by westconn1 View Post
    i should have also told you that for F15 i left 2 spaces in the content DFRCL AC, to populate the centre part, though in hindsight it would have been just as easy to have the cell empty and concatenate the whole string in the code
    as in some places i was just guessing as to the desired result you will have to fix some parts

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,778

    Re: Macro for tab wise invoice creation

    tmp.Cells(15, 6) = Replace(tmp.Cells(15, 6), " ", " " & t.Name & " ")
    replace this line with
    Code:
            tmp.Cells(15, 6) = "DFRCL " & t.Name & " AC"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Member
    Join Date
    Jul 2010
    Posts
    55

    Re: Macro for tab wise invoice creation

    It worked ..thank you very much


    i want to get first code on each tab to CELL A16

    how do i do that..



    Name:  ZLA.jpg
Views: 44
Size:  30.8 KB

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,778

    Re: Macro for tab wise invoice creation

    try like
    Code:
            tmp.Cells(16, 1) = Left(t.Name, InStr(t.Name, " ") - 1)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width