Results 1 to 28 of 28

Thread: [RESOLVED] Macro for tab wise invoice creation

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Resolved [RESOLVED] Macro for tab wise invoice creation

    I want to develop a macro to prepare invoices as per below invoice template (D tab).


    Name:  custom 1.jpg
Views: 268
Size:  52.2 KB


    Data is extracted from ZZ tab. which is below.

    No 1 data in D tab is extracted from ZZ tab -1

    No 2 data in D tab is extracted from ZZ tab - from Row 60

    No 3 data in D tab is extracted from ZZ tab - from Row 51


    ZZ Tab

    Name:  custom 2.jpg
Views: 281
Size:  66.4 KB


    for each colum need to create invoice as a new tab
    please give a macro for this

    i have attached the excell as well here
    Attached Files Attached Files

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    please help

  3. #3
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    A few things that can help you get this done:

    https://learn.microsoft.com/en-us/of...-vba-in-office
    https://www.automateexcel.com/vba/ad...me-worksheets/
    https://www.automateexcel.com/vba/loop-through-range/
    https://www.automateexcel.com/macros...acro-in-excel/

    to help you figure out problems https://www.excel-easy.com/vba/examples/debugging.html


    Once you have some code together, if you have any issues. Post the code and what the issue is you're facing and where in the code the problem is.

    Good luck with your project!

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    I not a good code write
    It will take ages to learn and write this for me

  5. #5
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,246

    Re: Macro for tab wise invoice creation

    I'll do it for you for $100,000.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Omg ??

  7. #7
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    Quote Originally Posted by dinukamp View Post
    I not a good code write
    It will take ages to learn and write this for me
    check out this code

    Code:
    ' this is here so that the button code and the copy data code
    ' can both access the sheet object
    Dim zzSheet As Worksheet
    
    Private Sub btnCreateSheets_Click()
    
        ' example of looping through the ZZ sheet to create a sheet for each column
        Dim invoiceSheet As Worksheet
        Dim invoiceTemplateRange As Range
        Dim zzSheetColumnCount As Integer
        Dim zzSheetCol As Long
        
        ' assign ZZ sheet to a variable
        Set zzSheet = ThisWorkbook.Sheets("ZZ")
        zzSheetColumnCount = zzSheet.UsedRange.Columns.Count
        
        ' the D sheet is already available and contains the template for the invoice
        Set invoiceSheet = ThisWorkbook.Sheets("D")
        
        ' clear any data in D
        invoiceSheet.Range("B15").Value = ""
        invoiceSheet.Range("G10").Value = ""
        invoiceSheet.Range("D21:D32").Value = ""
        
        DoEvents
        
        ' copy the template from D to be used when creating new sheets
        Set invoiceTemplateRange = invoiceSheet.Range("InvoiceTemplate")
        invoiceTemplateRange.Copy
        
        CopyDataFromZZToInvoice "D", invoiceSheet
        
        ' this turns off Excel displaying edits to the workbook as they happen
        ' making the code run faster
        Application.ScreenUpdating = False
        
        ' loop through the remainder of the columns in ZZ sheet
        ' to create the sheets, copy the template, and fill the data
        For zzSheetCol = 5 To zzSheetColumnCount
            
            ' add the new worksheet after the last one in the workbook
            Set invoiceSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            
            ' assign the column letter as the name of the new sheet
            invoiceSheet.Name = Split((zzSheet.Columns(zzSheetCol).Address(, 0)), ":")(0)
            
            ' paste the template
            With invoiceSheet.Range("B10")
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues, , False, False
                .PasteSpecial xlPasteFormats, , False, False
            End With
            
            ' call the procedure to copy the data
            CopyDataFromZZToInvoice invoiceSheet.Name, invoiceSheet
          
        Next zzSheetCol
        
        ' release the objects used to access the worksheets
        Set invoiceTemplateRange = Nothing
        Set invoiceSheet = Nothing
        Set zzSheet = Nothing
        
        ' this turns it back on
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub CopyDataFromZZToInvoice(fromColumn As String, toSheet As Worksheet)
        
        ' this sub procedure handles copying the data from ZZ to the sheet passed
        Dim zzSheetRow As Integer
        Dim toSheetRow As Integer
        
        ' copy section for item 1
        For zzSheetRow = 30 To 33
            toSheet.Cells(zzSheetRow - 9, "D").Value = zzSheet.Cells(zzSheetRow, fromColumn).Value
        
        Next zzSheetRow
        
        For zzSheetRow = 35 To 38
            toSheet.Cells(zzSheetRow - 6, "D").Value = zzSheet.Cells(zzSheetRow, fromColumn).Value
        
        Next zzSheetRow
        
        ' copy data to B15 in template from row 60 of the current column in zz
        toSheet.Range("B15").Value = zzSheet.Cells(60, fromColumn).Value
        
        ' copy data to G10 in template from row 51 of the current column in zz
        toSheet.Range("G10").Value = zzSheet.Cells(51, fromColumn).Value
    
    End Sub
    Attached Images Attached Images  
    Attached Files Attached Files

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Thanks a ton man! your a saviour

    small issue..how to keep the formula in this column when creating new tabs. please refer the highlighted please.


    Name:  custom 3.jpg
Views: 216
Size:  47.6 KB

  9. #9
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    Opps, add this
    Code:
    .PasteSpecial xlPasteFormulas, , False, False
    to the code that pastes the template to the new sheet

    edit: You're welcome, take some time to learn the code. I hope it gives you a launching pad to learn how to take care of things in the future.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Thank you! i want to add an image to the template. how do i amend the code to accommodate that. please advice.

    Name:  custom 4.jpg
Views: 209
Size:  33.2 KB

  11. #11
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    6,075

    Re: Macro for tab wise invoice creation

    Just add the image to the template and save the template.
    No need to write code for this and add the image each time the macro is running

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    i did but it didnt work. image is not getting copied. see the attachment.VBForums_Custom OT.zip

  13. #13
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    Use this code:

    Add this to the Dim area at the top of the button code
    Code:
        Dim invoiceTemplateLogo As Shape
    change the path F:\Temp\logo.jpg to point to where your logo file is located.

    Code:
            ' add the Logo to the new invoice sheet
            Set invoiceTemplateLogo = invoiceSheet.Shapes.AddPicture("F:\Temp\logo.jpg", False, True, 20, 20, -1, -1)
    
            With invoiceTemplateLogo
                .Top = invoiceSheet.Cells(10, 5).Top 'according to variables from correct answer
                .Left = invoiceSheet.Cells(10, 5).Left
                .LockAspectRatio = msoFalse
            End With
    
           Set invoiceTemplateLogo = Nothing
    put this code just after:
    Code:
            ' paste the template
            With invoiceSheet.Range("B10")
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues, , False, False
                .PasteSpecial xlPasteFormats, , False, False
                .PasteSpecial xlPasteFormulas, , False, False
            End With
    
            ' call the procedure to copy the data
            CopyDataFromZZToInvoice invoiceSheet.Name, invoiceSheet
    It will add the picture to the sheet at row 10 col E

    edit: added Set invoiceTemplateLogo = Nothing

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    When i extend the invoice area (=D!$A$1:$G$39) like below
    Name:  Capture.jpg
Views: 177
Size:  61.5 KB

    macro coping data alignments goes off
    where do I need to amend to correct this?

    Name:  Capture1.jpg
Views: 185
Size:  55.0 KB

  15. #15
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    Look at the code that writes the data to the new sheet, modify the row numbers that are used.

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    I have changed the invoice template range. and I want to past the data from A1 cell.

    please tell me where I should change and what...tried but I couldn't figure it out

    Name:  Capture.jpg
Views: 167
Size:  55.5 KB


    Name:  Camera.jpg
Views: 166
Size:  37.0 KB

  17. #17
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    You're saying that the template now starts in A1 and not at row 10? You'll have to modify the procedure CopyDataFromZZToInvoice so that is copies to the proper locations.

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Yes Please..
    I can understand the green color ticks below. but can not understand where I need to amend to template past in A1

    Name:  Capture.jpg
Views: 168
Size:  28.9 KB
    Last edited by dinukamp; May 25th, 2024 at 09:54 AM.

  19. #19
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    This part here, in btnCreateSheets_Click(), creates the new invoice sheet, then pastes the template to it.

    Code:
            ' add the new worksheet after the last one in the workbook
            Set invoiceSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            
            ' assign the column letter as the name of the new sheet
            invoiceSheet.Name = Split((zzSheet.Columns(zzSheetCol).Address(, 0)), ":")(0)
            
            ' paste the template
            With invoiceSheet.Range("B10")
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues, , False, False
                .PasteSpecial xlPasteFormats, , False, False
                .PasteSpecial xlPasteFormulas, , False, False
            End With
    Change B10 to A1

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Thank you! Its done...

    how do I convert all the excel tabs starting from D to one PDF file

    print area should be A1:G39 which should be fit to one page.

  21. #21
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    Check out Save Range as PDF here https://exceloffthegrid.com/vba-code...l-file-as-pdf/ and place the code to do so after the sheet has been filled in
    Code:
            ' call the procedure to copy the data
            CopyDataFromZZToInvoice invoiceSheet.Name, invoiceSheet

  22. #22

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Smile Re: Macro for tab wise invoice creation

    Thank you

    How to set the printing range for below code?
    I added below code but it didn't work
    Set rng = ws.Range("A1:G39")

    &

    Also i need to exclude ZZ sheet from the single pdf file which is saved

    need to save as a single pdf file


    HTML Code:
    Sub Printddd2()
    '
    ' Printddd Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+I
    '
    'Create and assign variables
        Dim saveLocation As String
        saveLocation = "D:\COSCO\Custom OT\myPDFFile.pdf"
        
        
    'Save Active Sheet(s) as PDF
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=saveLocation
    Last edited by dinukamp; May 28th, 2024 at 09:05 AM.

  23. #23

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Hi
    i used this macro for the above printing requirement but it doesn't work properly

    I need all tabs to be saved as a single PDF excluding ZZ tab.

    Print area of each tab is A1:G39

    Please help


    Code:
    Sub Printddd2()
    '
    ' Printddd Macro'
    ' Keyboard Shortcut: Ctrl+Shift+I'
    'Create and assign variables
        Dim saveLocation As String
        saveLocation = "D:\myPDFFile.pdf"
        
        Dim ws As Worksheet
        Dim printRange As Range
        
    'Save Active Sheet(s) as PDF
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "ZZ" Then
                Set printRange = ws.Range("$A$1:$G$39")
                With ws.PageSetup
                    .PrintArea = printRange.Address
                    .FitToPagesWide = 1
                    .FitToPagesTall = False
                End With
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
            End If
        Next ws
       
    End Sub

  24. #24
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    I'm glad you tried, here is code I used to export the sheet after it has been created.

    This goes in the loop just above "Next zzSheetCol"
    Code:
            ' export the sheet to its own PDF file
            ExportInvoiceSheetToPDF invoiceSheet
    which calls this sub routine that can be placed anywhere in the same module
    Code:
    Private Sub ExportInvoiceSheetToPDF(invoiceSheet As Worksheet)
    
        ' take the sheet passed and export the invoice range to a PDF
        ' of the same name
        
        Dim pdfFileName As String
        Dim pdfRange As Range
        
        ' create the file name to save it to
        pdfFileName = "F:\Temp\" & Format(Date, "MM_dd_yyyy") & " Sheet " & invoiceSheet.Name & ".pdf"
        
        ' set the range to export from
        Set pdfRange = invoiceSheet.Range("A1:G39")
        pdfRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, OpenAfterPublish:=False ' export the range
        
        Set pdfRange = Nothing
    
    End Sub
    change the pdfFile path variable to save the file to the location you need. EDIT: also set whatever properties you need when exporting the range.
    EDIT2: removed colon from filename
    Last edited by jdelano; May 29th, 2024 at 06:20 AM.

  25. #25

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    Thank you for kind your words

    need to save all tabs as one pdf. above creates single sheets for each page.

    Also A1: G39 not fitting to one page. Could you please help.

    does not fit to one page
    Attachment 191802

    individual page saved for each tab

    Name:  Capture 2 indi.jpg
Views: 76
Size:  46.6 KB

  26. #26
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: Macro for tab wise invoice creation

    My mistake I misunderstood, sorry about that, see the mods in the for loop as well as add Dim printRange As Range to the top of btnCreateSheets_Click()

    Code:
        For zzSheetCol = 5 To zzSheetColumnCount
            
            ' add the new worksheet after the last one in the workbook
            Set invoiceSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            
            ' set up the print range on each worksheet added   
            Set printRange = invoiceSheet.Range("$A$1:$G$39")
            With invoiceSheet.PageSetup
                .PrintArea = printRange.Address
                .FitToPagesWide = 1
                .FitToPagesTall = False
            End With
                
            ' assign the column letter as the name of the new sheet
            invoiceSheet.Name = Split((zzSheet.Columns(zzSheetCol).Address(, 0)), ":")(0)
            
            ' paste the template
            With invoiceSheet.Range("B10")
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues, , False, False
                .PasteSpecial xlPasteFormats, , False, False
                .PasteSpecial xlPasteFormulas, , False, False
            End With
    
            ' call the procedure to copy the data
            CopyDataFromZZToInvoice invoiceSheet.Name, invoiceSheet
          
            ' add the Logo to the new invoice sheet
            Set invoiceTemplateLogo = invoiceSheet.Shapes.AddPicture("F:\Temp\logo.jpg", False, True, 20, 20, -1, -1)
    
            With invoiceTemplateLogo
                .Top = invoiceSheet.Cells(10, 5).Top 'according to variables from correct answer
                .Left = invoiceSheet.Cells(10, 5).Left
                .LockAspectRatio = msoFalse
            End With
    
            Set invoiceTemplateLogo = Nothing
            Set printRange = Nothing    
            
        Next zzSheetCol
        
        ' release the objects used to access the worksheets
        Set invoiceTemplateRange = Nothing
        Set invoiceSheet = Nothing
        
        ' export the sheets to a PDF file
        zzSheet.Visible = xlSheetHidden  ' hide the ZZ tab from being included with the PDF
        
        ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="F:\Temp\Invoices created " & Format(Date, "MM-dd-yyy") & ".pdf"
        
        zzSheet.Visible = xlSheetVisible
        
        Set zzSheet = Nothing
    You'll need to tweak the settings to ensure each page fits, change the margins of the page setup.

  27. #27

    Thread Starter
    Lively Member
    Join Date
    Jul 2010
    Posts
    82

    Re: Macro for tab wise invoice creation

    hay Thanks a ton

    Thanks a lot for your kind help.

  28. #28
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    292

    Re: [RESOLVED] Macro for tab wise invoice creation

    You're welcome.

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