Results 1 to 19 of 19

Thread: [RESOLVED] Macro for tab wise invoice creation

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Resolved [RESOLVED] 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: 589
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
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    please help

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

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

    getting a error message

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

    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
    25,618

    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
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    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
    25,618

    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
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    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: 283
Size:  30.8 KB

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    need to change as per the below
    Cost sheet needs to be updated as per the below from each vessel sheet

    Name:  Capture1.jpg
Views: 212
Size:  48.2 KB



    Also need to change the cell as follows;

    Name:  Capture.jpg
Views: 236
Size:  41.5 KB

    VDA.zipVDA.zip
    Last edited by dinukamp; Dec 11th, 2018 at 02:51 AM.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Macro for tab wise invoice creation

    i am unable to follow exactly what you want to do, you
    need to clarify the details

    where does the number in I2 come from?
    are the values appended to the cost sheet or should a new one be created for each tab?

    i did look at this thread before, but did not find enough information to achieve any result
    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

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    I2 is a manually filled detail...I2 "TOS" number should come to the "invoice" tab C23 & C24 (Not the D2 "TOS" number)

    New cost sheet should be created for each tab let say "ZLA 59E cost sheet" same as the invoice..."ZLA 59E_INV"

    Hope image above in the filling of cost sheet is clear to you

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    I2 is a manually filled detail...I2 "TOS" number should come to the "invoice" tab C23 & C24 (Not the D2 "TOS" number)

    New cost sheet should be created for each tab let say "ZLA 59E cost sheet" same as the invoice..."ZLA 59E_INV"

    Hope image above in the filling of cost sheet is clear to you

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Macro for tab wise invoice creation

    I2 is a manually filled detail
    do i assume that the values in d2 and i 2 will be entered in the correct positions by the user? or does the code need to swap them?
    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

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Macro for tab wise invoice creation

    you can test this as i haven't
    Code:
    Set inv = Sheets("invoice")
    Set costsht = Sheets("cost sheet")
    For Each t In ThisWorkbook.Worksheets
        If  t.Name =costsht.name then exit for   ' should process all sheets before cost sheet
            inv.Copy , t
            Set tmp = Sheets(t.Index + 1)
            tmp.Name = t.Name & "_INV"
            tmp.Cells(15, 6) = "DFRCL " & t.Name & " AC"
            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)
            tmp.Cells(16, 1) = Left(t.Name, InStr(t.Name, " ") - 1)
            costsht.Copy , tmp
            Set tmp = Sheets(t.Index + 2)
            With tmp
                .Name = t.Name & "_cost_sheet"
                .Cells(2, 4) = t.Cells(2, 2)
                .Cells(1, 1) = t.Cells(4, 1)
                .Cells(2, 1) = t.Cells(5, 1)
                
                .Cells(25, 2) = t.Cells(2, 9)
                .Cells(26, 2) = t.Cells(2, 9)
                .Cells(27, 2) = t.Cells(2, 4)
                .Cells(28, 2) = t.Cells(2, 4)
                .Cells(29, 2) = t.Cells(2, 9)
                
                .Cells(25, 3) = t.Cells(21, 7)
                .Cells(26, 3) = t.Cells(46, 7) - t.Cells(21, 7) ' this may not be correct, bit of a guess
                .Cells(27, 3) = ""
                .Cells(28, 3) = t.Cells(26, 10)
                .Cells(29, 3) = ""
                
            End With
        
    Next
    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

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    Name:  pic1.jpg
Views: 214
Size:  38.1 KB

    Name:  pic2.jpg
Views: 227
Size:  37.9 KB

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    66

    Re: Macro for tab wise invoice creation

    Name:  pic1.jpg
Views: 214
Size:  38.1 KB

    Name:  pic2.jpg
Views: 227
Size:  37.9 KB

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Macro for tab wise invoice creation

    have a try to see if you can manage to some yourself
    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
  •  



Click Here to Expand Forum to Full Width