Consulting

Results 1 to 16 of 16

Thread: To automatically create a variable number of worksheets, name them and the enter the

  1. #1

    Question To automatically create a variable number of worksheets, name them and the enter the

    Hi experts,

    First of all, my level of Vba knowledge is very basic.

    This is what I would like to do:

    A person completes a form with personal IDs and an Invoice Number associated to it.

    It will be about 10 lines, but two or more people can have the same invoice number.

    So, for example:


    Line Name Total to charge Invoice #
    1 Donald Duck $234 INV001
    2 Patricio Donald $345 INV002
    3 Bilin Bilin Bilin (Tribilin) $45.70 INV001

    ...

    A person will manually enter the date for the Invoice # and in theory I could have between 1 and 10 different invoices.

    I will need to:
    - Create 1 worksheet for each invoice (please note that the number of invoices and therefore worksheets automatically created might be different each time)
    - Name each worksheet according to the name of the invoice
    - In each of the Invoice worksheets, create a table and formulas. The table and the formulas will be the same for each invoice worksheet.


    Is there anyone able to get me a start point on this please?

    Thank heaps

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    Create an invoice Template with the table and formulas.

    For each Invoice Name, copy the Template and rename it, then fill in the blanks
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Hi folks,

    I wrote a couple of weeks about this but now I have some of the macros written…

    How can I attach the file with my work so far?

    Everything starts when the user manually enters the info in the cells in blue:
    - NHI (to identify the person)
    - CAT (the client code)
    - Units to recover
    - …
    - Balance in funding to use

    The first macro, Macro1 of Module 1:
    - Creates the sheet Calculations
    - Counts and lists how many different Invoices there would be (in column Macros!K:K).
    - Creates a list of those many invoices and places them in Macros!A26:A33
    - Takes the “Units to Recover” in Macros!C:C, and splits them into one number with a decimal and then bigger, multiple of 10 figures, such as 50, 100, 500, 1000. To do that, considers that the total does not exceed the available (in column Macros!M)
    The second and third macros, Prepare_Invoices1 and Prepare_Invoices2() in Module 2:
    - Creates the sheet “Data for Invoices”
    - Creates a table for the Info in each of the tables of Calculations to go into so it can filter the blanks/empty values
    - You can see I need more understanding of looping because you might be easily able to do that with a loop but, due to my lack of knowledge, I needed to write the code for each of the tables in Calculations.
    - The filtered info is placed in Data for Invoices in I2, N2, S2….

    The fourth macro, Invoice_Making:

    - Create the sheet “Invoice Template”
    - Populates columns and formulas required for each template.
    And this is when I struggle with:

    This is what I need to do:

    1nd
    Copy the value of the first Cell in Macros!A24:A33 to cell $C$2 in the Invoice Template sheet
    Copy the value of the respective cells in the Data for Invoices, in this case, those ones that are in the list starting from I3 to L3 into the respective cells in the Invoice Template
    2nd
    I need to create a new sheet and name it according the Invoice, as showing in the Macros!A24:A33
    I need this to loop through all the values in the Macros!A24:A33.

    I started doing some coding to create the invoice pages… not sure how to go from there…
    I notice there might be an issue:
    When preparing the information to copy in the invoices, this is copied to the “Data for Invoices sheets but starting from different cells, e.g I2, N2, S2… AH2.
    This is because I was incapable to do this through a loop due to lack of knowledge…
    Perhaps doing this in a different way can help with the following part, the loop to create the invoices.

    Please help!!!!
    This has taking me a lot of learning, headaches, trial and error but I guess this is how we all started…


  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,946
    Location
    How can I attach the file with my work so far?
    Click [Go Advanced] bottom right and then the paper clip icon



    Please help!!!!
    This has taking me a lot of learning, headaches, trial and error but I guess this is how we all started…
    Instead of posting a screen shot, use the pound sign icon to insert [ CODE ] tags and paste the text of the macro between then

    And kudos for trying to write your own macros (that's the way to learn) as opposed to "Please write me a macro to ...."
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Perfect... I have added the file now... I believe
    Attached Files Attached Files

  6. #6
    VBAX Expert
    Joined
    Apr 2005
    Posts
    901
    Location
    This will add sheets to the end if a Sheet with that name does not exist in that Workbook.
    If there is a chance that there is a sheet with that name, what would you want to do?
    Delete it or skip adding a sheet with that name?
    Sub Add_Sheets_To_The_End()
    Dim i As Long, sh1 As Worksheet
    Set sh1 = Worksheets("Macros")
        For i = 24 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
            ThisWorkbook.Sheets.Add(, ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = sh1.Cells(i, 1).Value
        Next i
    End Sub

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,234
    What 's the need to produce almost identical sheets ?
    Isn't a worksheet containing 2^20 rows, and 2^14 columns (i.e. 2^34 cells) big enough to serve your purpose ?
    Although you work with a computer you still think in paper.

  8. #8
    The idea will be:

    - The user enters the information in the "Macros" sheet.
    - In that form, the user will enter between 1 to 10 different Invoice names (they can repeat or be a unique one).
    - The amount to recover will be split into a decimal and then multiples of 10. For example, the number 3456.25 will be split into 1000, 1000, 1000, 400, 50 and 6.25
    - The amount to recover cannot be more than the available amount (defined by the user), so if the available amount is only 1470, the 3456.25 of the previous line can only be split into 1000, 400, 50, 10 and 10.
    - Each of the amount of the table in Macros will "go" to a define "Invoice".
    - So far I managed to get the amount in one "base" sheet but now I need to either:
    Option a:
    - Create different sheets with the invoices names
    - Copy the specific information for a particular invoice from that "base" sheet to the respective Invoice sheet
    Option b:
    - Copy the information of a new sheet to a new sheet
    - Name that new sheet depending of the invoice the copied information is for
    Option c:
    - Any other more simple option

  9. #9
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    
    'Deletes all worksheets but Macros and Report
    
    
    Dim xWs As Worksheet
    
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
    
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "Macros" And xWs.Name <> "Report" Then
        xWs.Delete
        End If
        Next
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    
    
    
    
    
    
    'Getting values for Invoices numbers
    Range("K6:K15").Select
        Selection.Copy
        Range("A24").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("$A$24:$A$33").RemoveDuplicates Columns:=1, Header:=xlNo
        With Selection.Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("A24").Select
    
    
    
    
    
    
    'Creates Calculations worksheesheet
    
    
        
        Sheets.Add(After:=Sheets("Macros")).Name = "Calculations"
        
        Sheets("Calculations").Select
        
        Range("A2:W12").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$X$12"), , xlYes).Name = _
            "Table1"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "NHI"
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "Review date of allocation to be invoiced"
        Range("C2").Select
        ActiveCell.FormulaR1C1 = "Units Available"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "Units to invoice"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "Line 1"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "Line 2"
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "Line 3"
        Range("H2").Select
        ActiveCell.FormulaR1C1 = "Line 4"
        Range("I2").Select
        ActiveCell.FormulaR1C1 = "Line 5"
        Range("J2").Select
        ActiveCell.FormulaR1C1 = "Line 6"
        Range("K2").Select
        ActiveCell.FormulaR1C1 = "Line 7"
        Range("L2").Select
        ActiveCell.FormulaR1C1 = "Line 8"
        Range("M2").Select
        ActiveCell.FormulaR1C1 = "Line 9"
        Range("N2").Select
        ActiveCell.FormulaR1C1 = "Line 10"
        Range("O2").Select
        ActiveCell.FormulaR1C1 = "Line 11"
        Range("P2").Select
        ActiveCell.FormulaR1C1 = "Line 12"
        Range("Q2").Select
        ActiveCell.FormulaR1C1 = "Line 13"
        Range("R2").Select
        ActiveCell.FormulaR1C1 = "Line 14"
        Range("S2").Select
        ActiveCell.FormulaR1C1 = "Line 15"
        Range("T2").Select
        ActiveCell.FormulaR1C1 = "Line 16"
        Range("U2").Select
        ActiveCell.FormulaR1C1 = "Total Invoiced"
        Range("V2").Select
        ActiveCell.FormulaR1C1 = "Still to be invoiced"
        Range("W2").Select
        ActiveCell.FormulaR1C1 = "Invoice Number"
        Range("X2").Select
        ActiveCell.FormulaR1C1 = "CAT"
        Range("V3").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=RC[-18]-RC[-1]"
        
        
           
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C"
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[10]"
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "=INT((Macros!R[3]C[10]-Macros!R2C10)/10)"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[-1]"
        Range("E3").Select
        ActiveCell.FormulaR1C1 = "=RC[-1]-FLOOR(RC[-1], 10)"
        Range("F3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC[-3]>=500, RC4>=500), 500, (IF(AND(RC[-3]>=400, RC4>=400), 400, (IF(AND(RC[-3]>=300, RC4>=300), 300, (IF(AND(RC[-3]>=200, RC4>=200), 200,  (IF(AND(RC[-3]>=100, RC4>=100), 100, 0)       )    )))))))"
        Range("G3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("H3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("I3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("J3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("K3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("L3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("M3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("N3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("O3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("P3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("Q3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("R3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("S3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("T3").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(SUM(RC5:RC[-1])+RC[-1]<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, ((2*RC[-1])+SUM(RC5:RC[-1]))<RC3), RC[-1], IF(AND(SUM(RC5:RC[-1])+500<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=500, RC3-SUM(RC5:RC[-1])>=500), 500,  IF(AND(SUM(RC5:RC[-1])+400<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=400, RC3-SUM(RC5:RC[-1])>=400), 400, IF(AND(S" & _
            "UM(RC5:RC[-1])+300<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=300, RC3-SUM(RC5:RC[-1])>=300), 300,  IF(AND(SUM(RC5:RC[-1])+200<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=200, RC3-SUM(RC5:RC[-1])>=200), 200,  IF(AND(SUM(RC5:RC[-1])+100<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=100, RC3-SUM(RC5:RC[-1])>=100), 100,  IF(AND(SUM(RC5:RC[-" & _
            "1])+50<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=50, RC3-SUM(RC5:RC[-1])>=50), 50, IF(AND(SUM(RC5:RC[-1])+10<=RC4, SUM(RC5:RC[-1])<=RC4, SUM(RC5:RC[-1])<RC3, RC6>=10, RC3-SUM(RC5:RC[-1])>=10), 10, 0))))))))" & _
            ""
        Range("U3").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-16]:RC[-1])"
        Range("V3").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-18]-RC[-1]>0, RC[-18]-RC[-1], 0)"
        Range("W3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[-12]"
        Range("X3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[-22]"
        
        
        
        
    'Copy and transpose table
        Range("Table1[#All]").Select
        Selection.Copy
        Range("A14").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=True, Transpose:=True
        Range("A14").Select
        
       
    'Sort every column by large to smallest
        Range("B18:B33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "B18:B33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("B18:B33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("C18:C33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "C18:C33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("C18:C33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("D18:D33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "D18:D33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("D18:D33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("E18:E33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "E18:E33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("E18:E33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("F18:F33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "F18:F33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("F18:F33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("G18:G33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "G18:G33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("G18:G33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("H18:H33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "H18:H33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("H18:H33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("I18:I33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "I18:I33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("I18:I33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("J18:J33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "J18:J33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("J18:J33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("K18:K33").Select
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Calculations").Sort.SortFields.Add2 Key:=Range( _
            "K18:K33"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Calculations").Sort
            .SetRange Range("K18:K33")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        
    'Creates table for the info to be copied to
    
    
        
        Range("A42").Select
        ActiveCell.FormulaR1C1 = "Line 1"
        Range("A43").Select
        ActiveCell.FormulaR1C1 = "Line 2"
        Range("A44").Select
        ActiveCell.FormulaR1C1 = "Line 3"
        Range("A45").Select
        ActiveCell.FormulaR1C1 = "Line 4"
        Range("A46").Select
        ActiveCell.FormulaR1C1 = "Line 5"
        Range("A47").Select
        ActiveCell.FormulaR1C1 = "Line 6"
        Range("A48").Select
        ActiveCell.FormulaR1C1 = "Line 7"
        Range("A49").Select
        ActiveCell.FormulaR1C1 = "Line 8"
        Range("A50").Select
        ActiveCell.FormulaR1C1 = "Line 9"
        Range("A51").Select
        ActiveCell.FormulaR1C1 = "Line 10"
        Range("A52").Select
        ActiveCell.FormulaR1C1 = "Line 11"
        Range("A53").Select
        ActiveCell.FormulaR1C1 = "Line 12"
        Range("A54").Select
        ActiveCell.FormulaR1C1 = "Line 13"
        Range("A55").Select
        ActiveCell.FormulaR1C1 = "Line 14"
        Range("A56").Select
        ActiveCell.FormulaR1C1 = "Line 15"
        Range("A57").Select
        ActiveCell.FormulaR1C1 = "Line 16"
        
        
        Range("C41,E41,G41,I41,K41,M41,O41,Q41,S41,U41").Select
        Selection.FormulaR1C1 = "Units"
    
    
    
    
        Range("A41:U57").Select
        Selection.Copy
        ActiveWindow.SmallScroll Down:=7
        Range("A61").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A81").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A101").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A121").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A141").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A161").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A181").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A201").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("A221").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
            
    'Table for Invoice 1
     
        Range("A40").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B40").Select
        ActiveCell.FormulaR1C1 = "=Macros!R24C1"
    
    
        Range("A41:U57").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$41:$U$57"), , xlYes).Name = _
            "Table2"
    
    
        Range("B42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C2, R[-24]C2>0), R14C2, ""Empty"")"
        Range("C42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C2=R14C2, R36C2=R40C2, R[-24]C2>0), R[-24]C2, ""Empty"")"
        Range("D42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C3, R[-24]C3>0), R14C3, ""Empty"")"
        Range("E42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C4=R14C3, R36C3=R40C2, R[-24]C3>0), R[-24]C3, ""Empty"")"
        Range("F42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C4, R[-24]C4>0), R14C4, ""Empty"")"
        Range("G42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C6=R14C4, R36C4=R40C2, R[-24]C4>0), R[-24]C4, ""Empty"")"
        Range("H42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C5, R[-24]C5>0), R14C5, ""Empty"")"
        Range("I42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C8=R14C5, R36C5=R40C2, R[-24]C5>0), R[-24]C5, ""Empty"")"
        Range("J42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C6, R[-24]C6>0), R14C6, ""Empty"")"
        Range("K42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C10=R14C6, R36C6=R40C2, R[-24]C6>0), R[-24]C6, ""Empty"")"
        Range("L42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C7, R[-24]C7>0), R14C7, ""Empty"")"
        Range("M42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C12=R14C7, R36C7=R40C2, R[-24]C7>0), R[-24]C7, ""Empty"")"
        Range("N42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C8, R[-24]C8>0), R14C8, ""Empty"")"
        Range("O42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C14=R14C8, R36C8=R40C2, R[-24]C8>0), R[-24]C8, ""Empty"")"
        Range("P42").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R40C2=R36C9, R[-24]C9>0), R14C9, ""Empty"")"
        Range("Q42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C16=R14C9, R36C9=R40C2, R[-24]C9>0), R[-24]C9, ""Empty"")"
        Range("R42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R40C2=R36C10, R[-24]C10>0), R14C10, ""Empty"")"
        Range("S42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C18=R14C10, R36C10=R40C2, R[-24]C10>0), R[-24]C10, ""Empty"")"
        Range("T42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R40C2=R36C11, R[-24]C11>0), R14C11, ""Empty"")"
        Range("U42").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R42C20=R14C11, R36C11=R40C2, R[-24]C11>0), R[-24]C11, ""Empty"")"
        Range("A40").Select
        
    'Table for Invoice 2
    
    
        Range("A60").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B60").Select
        ActiveCell.FormulaR1C1 = "=Macros!R25C1"
    
    
        Range("A61:U77").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$61:$U$77"), , xlYes).Name = _
            "Table3"
            
        Range("B62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C2, R[-44]C2>0), R14C2, ""Empty"")"
        Range("C62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C2=R14C2, R36C2=R60C2, R[-44]C2>0), R[-44]C2, ""Empty"")"
        Range("D62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C3, R[-44]C3>0), R14C3, ""Empty"")"
        Range("E62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C4=R14C3, R36C3=R60C2, R[-44]C3>0), R[-44]C3, ""Empty"")"
        Range("F62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C4, R[-44]C4>0), R14C4, ""Empty"")"
        Range("G62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C6=R14C4, R36C4=R60C2, R[-44]C4>0), R[-44]C4, ""Empty"")"
        Range("H62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C5, R[-44]C5>0), R14C5, ""Empty"")"
        Range("I62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C8=R14C5, R36C5=R60C2, R[-44]C5>0), R[-44]C5, ""Empty"")"
        Range("J62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C6, R[-44]C6>0), R14C6, ""Empty"")"
        Range("K62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C10=R14C6, R36C6=R60C2, R[-44]C6>0), R[-44]C6, ""Empty"")"
        Range("L62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C7, R[-44]C7>0), R14C7, ""Empty"")"
        Range("M62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C12=R14C7, R36C7=R60C2, R[-44]C7>0), R[-44]C7, ""Empty"")"
        Range("N62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C8, R[-44]C8>0), R14C8, ""Empty"")"
        Range("O62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C14=R14C8, R36C8=R60C2, R[-44]C8>0), R[-44]C8, ""Empty"")"
        Range("P62").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R60C2=R36C9, R[-44]C9>0), R14C9, ""Empty"")"
        Range("Q62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C16=R14C9, R36C9=R60C2, R[-44]C9>0), R[-44]C9, ""Empty"")"
        Range("R62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R60C2=R36C10, R[-44]C10>0), R14C10, ""Empty"")"
        Range("S62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C18=R14C10, R36C10=R60C2, R[-44]C10>0), R[-44]C10, ""Empty"")"
        Range("T62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R60C2=R36C11, R[-44]C11>0), R14C11, ""Empty"")"
        Range("U62").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R62C20=R14C11, R36C11=R60C2, R[-44]C11>0), R[-44]C11, ""Empty"")"
        Range("U63").Select
            
    'Table for Invoice 3
    
    
        Range("A80").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B80").Select
        ActiveCell.FormulaR1C1 = "=Macros!R26C1"
            
        Range("A81:U97").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$81:$U$97"), , xlYes).Name = _
            "Table4"
        
        Range("B82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C2, R[-64]C2>0), R14C2, ""Empty"")"
        Range("C82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C2=R14C2, R36C2=R80C2, R[-64]C2>0), R[-64]C2, ""Empty"")"
        Range("D82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C3, R[-64]C3>0), R14C3, ""Empty"")"
        Range("E82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C4=R14C3, R36C3=R80C2, R[-64]C3>0), R[-64]C3, ""Empty"")"
        Range("F82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C4, R[-64]C4>0), R14C4, ""Empty"")"
        Range("G82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C6=R14C4, R36C4=R80C2, R[-64]C4>0), R[-64]C4, ""Empty"")"
        Range("H82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C5, R[-64]C5>0), R14C5, ""Empty"")"
        Range("I82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C8=R14C5, R36C5=R80C2, R[-64]C5>0), R[-64]C5, ""Empty"")"
        Range("J82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C6, R[-64]C6>0), R14C6, ""Empty"")"
        Range("K82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C10=R14C6, R36C6=R80C2, R[-64]C6>0), R[-64]C6, ""Empty"")"
        Range("L82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C7, R[-64]C7>0), R14C7, ""Empty"")"
        Range("M82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C12=R14C7, R36C7=R80C2, R[-64]C7>0), R[-64]C7, ""Empty"")"
        Range("N82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C8, R[-64]C8>0), R14C8, ""Empty"")"
        Range("O82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C14=R14C8, R36C8=R80C2, R[-64]C8>0), R[-64]C8, ""Empty"")"
        Range("P82").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R80C2=R36C9, R[-64]C9>0), R14C9, ""Empty"")"
        Range("Q82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C16=R14C9, R36C9=R80C2, R[-64]C9>0), R[-64]C9, ""Empty"")"
        Range("R82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R80C2=R36C10, R[-64]C10>0), R14C10, ""Empty"")"
        Range("S82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C18=R14C10, R36C10=R80C2, R[-64]C10>0), R[-64]C10, ""Empty"")"
        Range("T82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R80C2=R36C11, R[-64]C11>0), R14C11, ""Empty"")"
        Range("U82").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R82C20=R14C11, R36C11=R80C2, R[-64]C11>0), R[-64]C11, ""Empty"")"
        Range("B80").Select
       
       
    'Table for Invoice 4
        
        Range("A100").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B100").Select
        ActiveCell.FormulaR1C1 = "=Macros!R27C1"
             
        Range("A101:U117").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$101:$U$117"), , xlYes).Name = _
            "Table5"
        
        Range("B102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C2, R[-84]C2>0), R14C2, ""Empty"")"
        Range("C102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C2=R14C2, R36C2=R100C2, R[-84]C2>0), R[-84]C2, ""Empty"")"
        Range("D102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C3, R[-84]C3>0), R14C3, ""Empty"")"
        Range("E102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C4=R14C3, R36C3=R100C2, R[-84]C3>0), R[-84]C3, ""Empty"")"
        Range("F102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C4, R[-84]C4>0), R14C4, ""Empty"")"
        Range("G102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C6=R14C4, R36C4=R100C2, R[-84]C4>0), R[-84]C4, ""Empty"")"
        Range("H102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C5, R[-84]C5>0), R14C5, ""Empty"")"
        Range("I102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C8=R14C5, R36C5=R100C2, R[-84]C5>0), R[-84]C5, ""Empty"")"
        Range("J102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C6, R[-84]C6>0), R14C6, ""Empty"")"
        Range("K102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C10=R14C6, R36C6=R100C2, R[-84]C6>0), R[-84]C6, ""Empty"")"
        Range("L102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C7, R[-84]C7>0), R14C7, ""Empty"")"
        Range("M102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C12=R14C7, R36C7=R100C2, R[-84]C7>0), R[-84]C7, ""Empty"")"
        Range("N102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C8, R[-84]C8>0), R14C8, ""Empty"")"
        Range("O102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C14=R14C8, R36C8=R100C2, R[-84]C8>0), R[-84]C8, ""Empty"")"
        Range("P102").Select
        ActiveCell.FormulaR1C1 = "=IF(AND(R100C2=R36C9, R[-84]C9>0), R14C9, ""Empty"")"
        Range("Q102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C16=R14C9, R36C9=R100C2, R[-84]C9>0), R[-84]C9, ""Empty"")"
        Range("R102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R100C2=R36C10, R[-84]C10>0), R14C10, ""Empty"")"
        Range("S102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C18=R14C10, R36C10=R100C2, R[-84]C10>0), R[-84]C10, ""Empty"")"
        Range("T102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R100C2=R36C11, R[-84]C11>0), R14C11, ""Empty"")"
        Range("U102").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R102C20=R14C11, R36C11=R100C2, R[-84]C11>0), R[-84]C11, ""Empty"")"
        Range("B100").Select
        
    'Table for Invoice 5
    
    
        Range("A120").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B120").Select
        ActiveCell.FormulaR1C1 = "=Macros!R28C1"
       
        Range("A121:U137").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$121:$U$137"), , xlYes).Name = _
            "Table6"
            
        Range("B122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C2, R[-104]C2>0), R14C2, ""Empty"")"
        Range("C122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C2=R14C2, R36C2=R120C2, R[-104]C2>0), R[-104]C2, ""Empty"")"
        Range("D122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C3, R[-104]C3>0), R14C3, ""Empty"")"
        Range("E122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C4=R14C3, R36C3=R120C2, R[-104]C3>0), R[-104]C3, ""Empty"")"
        Range("F122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C4, R[-104]C4>0), R14C4, ""Empty"")"
        Range("G122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C6=R14C4, R36C4=R120C2, R[-104]C4>0), R[-104]C4, ""Empty"")"
        Range("H122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C5, R[-104]C5>0), R14C5, ""Empty"")"
        Range("I122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C8=R14C5, R36C5=R120C2, R[-104]C5>0), R[-104]C5, ""Empty"")"
        Range("J122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C6, R[-104]C6>0), R14C6, ""Empty"")"
        Range("K122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C10=R14C6, R36C6=R120C2, R[-104]C6>0), R[-104]C6, ""Empty"")"
        Range("L122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C7, R[-104]C7>0), R14C7, ""Empty"")"
        Range("M122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C12=R14C7, R36C7=R120C2, R[-104]C7>0), R[-104]C7, ""Empty"")"
        Range("N122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C8, R[-104]C8>0), R14C8, ""Empty"")"
        Range("O122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C14=R14C8, R36C8=R120C2, R[-104]C8>0), R[-104]C8, ""Empty"")"
        Range("P122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C9, R[-104]C9>0), R14C9, ""Empty"")"
        Range("Q122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C16=R14C9, R36C9=R120C2, R[-104]C9>0), R[-104]C9, ""Empty"")"
        Range("R122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C10, R[-104]C10>0), R14C10, ""Empty"")"
        Range("S122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C18=R14C10, R36C10=R120C2, R[-104]C10>0), R[-104]C10, ""Empty"")"
        Range("T122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R120C2=R36C11, R[-104]C11>0), R14C11, ""Empty"")"
        Range("U122").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R122C20=R14C11, R36C11=R120C2, R[-104]C11>0), R[-104]C11, ""Empty"")"
        Range("B120").Select
            
    'Table for Invoice 6
            
        Range("A140").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B140").Select
        ActiveCell.FormulaR1C1 = "=Macros!R29C1"
            
        Range("A141:U157").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$141:$U$157"), , xlYes).Name = _
            "Table7"
        
        Range("B142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C2, R[-124]C2>0), R14C2, ""Empty"")"
        Range("C142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C2=R14C2, R36C2=R140C2, R[-124]C2>0), R[-124]C2, ""Empty"")"
        Range("D142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C3, R[-124]C3>0), R14C3, ""Empty"")"
        Range("E142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C4=R14C3, R36C3=R140C2, R[-124]C3>0), R[-124]C3, ""Empty"")"
        Range("F142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C4, R[-124]C4>0), R14C4, ""Empty"")"
        Range("G142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C6=R14C4, R36C4=R140C2, R[-124]C4>0), R[-124]C4, ""Empty"")"
        Range("H142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C5, R[-124]C5>0), R14C5, ""Empty"")"
        Range("I142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C8=R14C5, R36C5=R140C2, R[-124]C5>0), R[-124]C5, ""Empty"")"
        Range("J142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C6, R[-124]C6>0), R14C6, ""Empty"")"
        Range("K142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C10=R14C6, R36C6=R140C2, R[-124]C6>0), R[-124]C6, ""Empty"")"
        Range("L142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C7, R[-124]C7>0), R14C7, ""Empty"")"
        Range("M142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C12=R14C7, R36C7=R140C2, R[-124]C7>0), R[-124]C7, ""Empty"")"
        Range("N142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C8, R[-124]C8>0), R14C8, ""Empty"")"
        Range("O142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C14=R14C8, R36C8=R140C2, R[-124]C8>0), R[-124]C8, ""Empty"")"
        Range("P142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C9, R[-124]C9>0), R14C9, ""Empty"")"
        Range("Q142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C16=R14C9, R36C9=R140C2, R[-124]C9>0), R[-124]C9, ""Empty"")"
        Range("R142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C10, R[-124]C10>0), R14C10, ""Empty"")"
        Range("S142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C18=R14C10, R36C10=R140C2, R[-124]C10>0), R[-124]C10, ""Empty"")"
        Range("T142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R140C2=R36C11, R[-124]C11>0), R14C11, ""Empty"")"
        Range("U142").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R142C20=R14C11, R36C11=R140C2, R[-124]C11>0), R[-124]C11, ""Empty"")"
        Range("B140").Select
        
        
        
    'Table for Invoice 7
    
    
        Range("A160").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B160").Select
        ActiveCell.FormulaR1C1 = "=Macros!R30C1"
    
    
        Range("A161:U177").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$161:$U$177"), , xlYes).Name = _
            "Table8"
        
        
        Range("B162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C2, R[-144]C2>0), R14C2, ""Empty"")"
        Range("C162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C2=R14C2, R36C2=R160C2, R[-144]C2>0), R[-144]C2, ""Empty"")"
        Range("D162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C3, R[-144]C3>0), R14C3, ""Empty"")"
        Range("E162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C4=R14C3, R36C3=R160C2, R[-144]C3>0), R[-144]C3, ""Empty"")"
        Range("F162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C4, R[-144]C4>0), R14C4, ""Empty"")"
        Range("G162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C6=R14C4, R36C4=R160C2, R[-144]C4>0), R[-144]C4, ""Empty"")"
        Range("H162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C5, R[-144]C5>0), R14C5, ""Empty"")"
        Range("I162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C8=R14C5, R36C5=R160C2, R[-144]C5>0), R[-144]C5, ""Empty"")"
        Range("J162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C6, R[-144]C6>0), R14C6, ""Empty"")"
        Range("K162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C10=R14C6, R36C6=R160C2, R[-144]C6>0), R[-144]C6, ""Empty"")"
        Range("L162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C7, R[-144]C7>0), R14C7, ""Empty"")"
        Range("M162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C12=R14C7, R36C7=R160C2, R[-144]C7>0), R[-144]C7, ""Empty"")"
        Range("N162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C8, R[-144]C8>0), R14C8, ""Empty"")"
        Range("O162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C14=R14C8, R36C8=R160C2, R[-144]C8>0), R[-144]C8, ""Empty"")"
        Range("P162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C9, R[-144]C9>0), R14C9, ""Empty"")"
        Range("Q162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C16=R14C9, R36C9=R160C2, R[-144]C9>0), R[-144]C9, ""Empty"")"
        Range("R162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C10, R[-144]C10>0), R14C10, ""Empty"")"
        Range("S162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C18=R14C10, R36C10=R160C2, R[-144]C10>0), R[-144]C10, ""Empty"")"
        Range("T162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R160C2=R36C11, R[-144]C11>0), R14C11, ""Empty"")"
        Range("U162").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R162C20=R14C11, R36C11=R160C2, R[-144]C11>0), R[-144]C11, ""Empty"")"
        Range("B160").Select
        
    'Table for Invoice 8
    
    
        Range("A180").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B180").Select
        ActiveCell.FormulaR1C1 = "=Macros!R31C1"
          
        Range("A181:U197").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$181:$U$197"), , xlYes).Name = _
            "Table9"
        
        Range("B182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C2, R[-164]C2>0), R14C2, ""Empty"")"
        Range("C182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C2=R14C2, R36C2=R180C2, R[-164]C2>0), R[-164]C2, ""Empty"")"
        Range("D182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C3, R[-164]C3>0), R14C3, ""Empty"")"
        Range("E182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C4=R14C3, R36C3=R180C2, R[-164]C3>0), R[-164]C3, ""Empty"")"
        Range("F182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C4, R[-164]C4>0), R14C4, ""Empty"")"
        Range("G182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C6=R14C4, R36C4=R180C2, R[-164]C4>0), R[-164]C4, ""Empty"")"
        Range("H182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C5, R[-164]C5>0), R14C5, ""Empty"")"
        Range("I182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C8=R14C5, R36C5=R180C2, R[-164]C5>0), R[-164]C5, ""Empty"")"
        Range("J182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C6, R[-164]C6>0), R14C6, ""Empty"")"
        Range("K182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C10=R14C6, R36C6=R180C2, R[-164]C6>0), R[-164]C6, ""Empty"")"
        Range("L182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C7, R[-164]C7>0), R14C7, ""Empty"")"
        Range("M182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C12=R14C7, R36C7=R180C2, R[-164]C7>0), R[-164]C7, ""Empty"")"
        Range("N182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C8, R[-164]C8>0), R14C8, ""Empty"")"
        Range("O182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C14=R14C8, R36C8=R180C2, R[-164]C8>0), R[-164]C8, ""Empty"")"
        Range("P182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C9, R[-164]C9>0), R14C9, ""Empty"")"
        Range("Q182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C16=R14C9, R36C9=R180C2, R[-164]C9>0), R[-164]C9, ""Empty"")"
        Range("R182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C10, R[-164]C10>0), R14C10, ""Empty"")"
        Range("S182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C18=R14C10, R36C10=R180C2, R[-164]C10>0), R[-164]C10, ""Empty"")"
        Range("T182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R180C2=R36C11, R[-164]C11>0), R14C11, ""Empty"")"
        Range("U182").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R182C20=R14C11, R36C11=R180C2, R[-164]C11>0), R[-164]C11, ""Empty"")"
        Range("B180").Select
        
        
    'Table for Invoice 9
    
    
        Range("A200").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B200").Select
        ActiveCell.FormulaR1C1 = "=Macros!R32C1"
     
        Range("A201:U217").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$201:$U$217"), , xlYes).Name = _
            "Table10"
            
        Range("B202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C2, R[-184]C2>0), R14C2, ""Empty"")"
        Range("C202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C2=R14C2, R36C2=R200C2, R[-184]C2>0), R[-184]C2, ""Empty"")"
        Range("D202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C3, R[-184]C3>0), R14C3, ""Empty"")"
        Range("E202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C4=R14C3, R36C3=R200C2, R[-184]C3>0), R[-184]C3, ""Empty"")"
        Range("F202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C4, R[-184]C4>0), R14C4, ""Empty"")"
        Range("G202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C6=R14C4, R36C4=R200C2, R[-184]C4>0), R[-184]C4, ""Empty"")"
        Range("H202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C5, R[-184]C5>0), R14C5, ""Empty"")"
        Range("I202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C8=R14C5, R36C5=R200C2, R[-184]C5>0), R[-184]C5, ""Empty"")"
        Range("J202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C6, R[-184]C6>0), R14C6, ""Empty"")"
        Range("K202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C10=R14C6, R36C6=R200C2, R[-184]C6>0), R[-184]C6, ""Empty"")"
        Range("L202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C7, R[-184]C7>0), R14C7, ""Empty"")"
        Range("M202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C12=R14C7, R36C7=R200C2, R[-184]C7>0), R[-184]C7, ""Empty"")"
        Range("N202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C8, R[-184]C8>0), R14C8, ""Empty"")"
        Range("O202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C14=R14C8, R36C8=R200C2, R[-184]C8>0), R[-184]C8, ""Empty"")"
        Range("P202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C9, R[-184]C9>0), R14C9, ""Empty"")"
        Range("Q202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C16=R14C9, R36C9=R200C2, R[-184]C9>0), R[-184]C9, ""Empty"")"
        Range("R202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C10, R[-184]C10>0), R14C10, ""Empty"")"
        Range("S202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C18=R14C10, R36C10=R200C2, R[-184]C10>0), R[-184]C10, ""Empty"")"
        Range("T202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R200C2=R36C11, R[-184]C11>0), R14C11, ""Empty"")"
        Range("U202").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R202C20=R14C11, R36C11=R200C2, R[-184]C11>0), R[-184]C11, ""Empty"")"
        Range("B200").Select
            
    'Table for Invoice 10
           
        Range("A220").Select
        ActiveCell.FormulaR1C1 = "Table for Invoice"
        Range("B220").Select
        ActiveCell.FormulaR1C1 = "=Macros!R33C1"
            
        Range("A221:U237").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$221:$U$237"), , xlYes).Name = _
            "Table11"
            
        Range("B222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C2, R[-204]C2>0), R14C2, ""Empty"")"
        Range("C222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C2=R14C2, R36C2=R220C2, R[-204]C2>0), R[-204]C2, ""Empty"")"
        Range("D222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C3, R[-204]C3>0), R14C3, ""Empty"")"
        Range("E222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C4=R14C3, R36C3=R220C2, R[-204]C3>0), R[-204]C3, ""Empty"")"
        Range("F222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C4, R[-204]C4>0), R14C4, ""Empty"")"
        Range("G222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C6=R14C4, R36C4=R220C2, R[-204]C4>0), R[-204]C4, ""Empty"")"
        Range("H222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C5, R[-204]C5>0), R14C5, ""Empty"")"
        Range("I222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C8=R14C5, R36C5=R220C2, R[-204]C5>0), R[-204]C5, ""Empty"")"
        Range("J222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C6, R[-204]C6>0), R14C6, ""Empty"")"
        Range("K222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C10=R14C6, R36C6=R220C2, R[-204]C6>0), R[-204]C6, ""Empty"")"
        Range("L222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C7, R[-204]C7>0), R14C7, ""Empty"")"
        Range("M222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C12=R14C7, R36C7=R220C2, R[-204]C7>0), R[-204]C7, ""Empty"")"
        Range("N222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C8, R[-204]C8>0), R14C8, ""Empty"")"
        Range("O222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C14=R14C8, R36C8=R220C2, R[-204]C8>0), R[-204]C8, ""Empty"")"
        Range("P222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C9, R[-204]C9>0), R14C9, ""Empty"")"
        Range("Q222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C16=R14C9, R36C9=R220C2, R[-204]C9>0), R[-204]C9, ""Empty"")"
        Range("R222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C10, R[-204]C10>0), R14C10, ""Empty"")"
        Range("S222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C18=R14C10, R36C10=R220C2, R[-204]C10>0), R[-204]C10, ""Empty"")"
        Range("T222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R220C2=R36C11, R[-204]C11>0), R14C11, ""Empty"")"
        Range("U222").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(R222C20=R14C11, R36C11=R220C2, R[-204]C11>0), R[-204]C11, ""Empty"")"
        Range("B220").Select
        
        
        
    'To fill tables titles
        
        
        Range("A41,A61,A81,A101,A121,A141,A161,A181,A201,A221").Select
        Selection.FormulaR1C1 = "Line Number"
        Range("B41,B61,B81,B101,B121,B141,B161,B181,B201,B221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("D41,D61,D81,D101,D121,D141,D161,D181,D201,D221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("F41,F61,F81,F101,F121,F141,F161,F181,F201,F221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("H41,H61,H81,H101,H121,H141,H161,H181,H201,H221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("J41,J61,J81,J101,J121,J141,J161,J181,J201,J221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("L41,L61,L81,L101,L121,L141,L161,L181,L201,L221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("N41,N61,N81,N101,N121,N141,N161,N181,N201,N221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("P41,P61,P81,P101,P121,P141,P161,P181,P201,P221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("R41,R61,R81,R101,R121,R141,R161,R181,R201,R221").Select
        Selection.FormulaR1C1 = "NHI"
        Range("T41,T61,T81,T101,T121,T141,T161,T181,T201,T221").Select
        Selection.FormulaR1C1 = "NHI"
        
        
        Sheets("Macros").Select
        Range("C19").Select
        
        ActiveSheet.Pictures.Insert( _
            "https://cdn.hubblecontent.osi.office.net/icons/publish/icons_checkmark/checkmark.svg" _
            ).Select
        Selection.ShapeRange.ScaleWidth 0.4650207787, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.4650207787, msoFalse, _
            msoScaleFromBottomRight
        Selection.ShapeRange.IncrementLeft -11.1111023622
        Selection.ShapeRange.IncrementTop -49.6296062992
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent6
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.25
            .Transparency = 0
            .Solid
        End With
        
        
        MsgBox "Please proceed to next step" & vbCrLf & "Thank you", vbOKOnly + vbInformation
        
        
    End Sub
    Last edited by chiwidan; 07-22-2021 at 09:30 PM.

  10. #10
    Sub Prepare_Invoices1()
    
    
        
    'Filter Empty Cells
        Sheets.Add(After:=Sheets("Calculations")).Name = "Data for Invoices"
        Sheets("Data for Invoices").Select
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "NHI"
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "Units"
        Range("C2").Select
        ActiveCell.FormulaR1C1 = "Invoice Number"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "CAT"
        Range("A2:D162").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$D$162"), , xlYes).Name = _
            "Table12"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(INDEX(R3C[3]:R12C[3],MATCH(RC1,R3C6:R12C6,0)),0)"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "NHI"
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "CAT"
        Range("F2:G12").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$F$2:$G$12"), , xlYes).Name = _
            "Table13"
        Range("F3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[-5]"
        Range("G3").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[3]C[-5]"
    
    
    
    
    'Start Invoice 1
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table2[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table2[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table2[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B40").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("I2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
            
     'End Invoice 1 start Invoice 2
      
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table3[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table3[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table3[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B60").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("N2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
        
    'End Invoice 2 and Start Invoice 3
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table4[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table4[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table4[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B80").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("S2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End Invoice 3 and Start Invoice 4
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table5[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table5[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table5[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("B100").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("X2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End Invoice 4 and Start Invoice 5
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table6[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table6[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table6[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B120").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AC2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    
    
    'End Invoice 5
    
    
        Sheets("Macros").Select
        Range("D19").Select
    
    
        ActiveSheet.Shapes.Range(Array("Graphic 17")).Select
        Selection.Copy
        Range("D23").Select
        ActiveSheet.Paste
        Selection.ShapeRange.IncrementLeft 82.2222047244
        Selection.ShapeRange.IncrementTop -68.8888976378
    
    
    
    
        MsgBox "Please proceed to step 3" & vbCrLf & "Thank you", vbOKOnly + vbInformation
    
    
    End Sub
    
    
    Sub Prepare_Invoices2()
    
    
    
    
    'Start of Invoice 6
    
    
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table7[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    
    
        Sheets("Calculations").Select
        Range("Table7[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table7[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B140").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AH2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End of Invoice 6 and Start of Invoice 7
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table8[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table8[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table8[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B160").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AM2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End Invoice 7 and start Invoice 8
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table9[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table9[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table9[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B180").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AR2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End Invoice 8 start Invoice 9
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table10[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        
        
        
        Sheets("Calculations").Select
        Range("Table10[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table10[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        
        Sheets("Calculations").Select
        Range("B200").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AM2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    'End Invoice 9 and Start Invoice 10
    
    
        Range("A3").Select
        Sheets("Calculations").Select
        Range("Table11[NHI]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI2]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI3]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI5]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI7]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI9]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[NHI10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("A147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Calculations").Select
        Range("Table11[Units]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units4]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B19").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units6]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B35").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units8]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B51").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units10]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B67").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units12]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B83").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units14]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B99").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units16]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B115").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units18]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B131").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("Calculations").Select
        Range("Table11[Units20]").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("B147").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Sheets("Calculations").Select
        Range("B220").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    
    
        Range("Table12[#Headers]").Select
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4, Criteria1:= _
            ">0", Operator:=xlAnd
        Range("Table12[[#Headers],[NHI]]").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("AW2").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
        ActiveSheet.ListObjects("Table12").Range.AutoFilter Field:=4
        Range("A3:C3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
    
    
    
    
        Sheets("Macros").Select
        Range("F19").Select
        
        ActiveSheet.Shapes.Range(Array("Graphic 20")).Select
        Selection.Copy
        ActiveSheet.Paste
        Selection.ShapeRange.IncrementLeft 154.8148031496
        Selection.ShapeRange.IncrementTop -14.0740944882
    
    
    
    
        MsgBox "Please proceed to step 4" & vbCrLf & "Thank you", vbOKOnly + vbInformation
    
    
    
    
    End Sub

  11. #11
    Sub Invoice_Making()
    
    
    
    
    'Creates the Invoice Information
        Sheets.Add(After:=Sheets("Data for Invoices")).Name = "Invoice Template"
        
        Sheets("Invoice Template").Select
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "Line Number"
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "NHI"
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "Name"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "CAT"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "F. Type 1"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "F. Type 2"
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "F. Type 3"
        Range("H2").Select
        ActiveCell.FormulaR1C1 = "F. Type 4"
        Range("E3").Select
        ActiveCell.FormulaR1C1 = 30
        Range("F3").Select
        ActiveCell.FormulaR1C1 = 30
        Range("G3").Select
        ActiveCell.FormulaR1C1 = 10
        Range("H3").Select
        ActiveCell.FormulaR1C1 = 4
        
        
        Range("I3").Select
        ActiveCell.FormulaR1C1 = "Total Cost"
        Range("J3").Select
        ActiveCell.FormulaR1C1 = " "
        Range("K3").Select
        ActiveCell.FormulaR1C1 = "Start"
        Range("L3").Select
        ActiveCell.FormulaR1C1 = "End"
        
        
        Range("P4").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[2]C[-15]"
        Range("Q4").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[2]C[-6]"
        Range("R4").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
        Range("S4").Select
        ActiveCell.FormulaR1C1 = "=Macros!R[2]C[-11]"
        Range("P3:S13").Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$P$3:$S$13"), , xlYes).Name = _
            "Table14"
       
        
        
        Range("I4").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]*R[-1]C[-2]"
        Range("K4").Select
        ActiveCell.Formula2R1C1 = _
            "=IFERROR(INDEX(R4C19:R13C19, MATCH(RC[-9]&R2C3, R4C18:R13C18), 0), 0)"
        Range("L4").Select
        ActiveCell.FormulaR1C1 = "=RC[-1]+7"
        
        
           
           
           
        
    
    
    End Sub
    Last edited by Paul_Hossler; 07-22-2021 at 09:57 PM. Reason: Added CODE tags

  12. #12
    Hi everyone,

    Apologies for the crosspost. Just learning.

    I have also asked this at:

    https://www.ozgrid.com/forum/index.p...78#post1251278

    and

    https://www.mrexcel.com/board/thread...f-them.1175635
    Last edited by Paul_Hossler; 07-22-2021 at 09:59 PM. Reason: Fixed URL tag formatting

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,946
    Location
    1. You should not Select things to just act on them. That's just Macro Recorder code; you need to really clean it up and possibly generalize it

    All the lines that are similar to ...


    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Line Number"

    ... could be written more efficiently and concisely as ...

    Range("A3").Value = "Line Number"
    2. All that code in 3-4 posts could be more useful to reviewer if it were in a workbook with sample data inputs and a sample of the desired output
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    thanks Paul, I will get onto it...

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,946
    Location
    All part of the learning process
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Expert
    Joined
    Apr 2005
    Posts
    901
    Location
    I see Paul got to you before this but here it is anyway.
    You don't need to select to enter data. As a matter of fact, it is frowned upon in most cases.
     Range("A2").Select
        ActiveCell.FormulaR1C1 = "NHI"
    should be
    Range("A2").Value = "NHI"
    This goes for all selecting where you put data or formulae in a cell.


    There is a continuation is this part. Use it to your advantage.
    Range("E2").Select
        ActiveCell.FormulaR1C1 = "Line 1"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "Line 2"
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "Line 3"
        Range("H2").Select
        ActiveCell.FormulaR1C1 = "Line 4"
        Range("I2").Select
        ActiveCell.FormulaR1C1 = "Line 5"
        Range("J2").Select
        ActiveCell.FormulaR1C1 = "Line 6"
        Range("K2").Select
        ActiveCell.FormulaR1C1 = "Line 7"
        Range("L2").Select
        ActiveCell.FormulaR1C1 = "Line 8"
        Range("M2").Select
        ActiveCell.FormulaR1C1 = "Line 9"
        Range("N2").Select
        ActiveCell.FormulaR1C1 = "Line 10"
        Range("O2").Select
        ActiveCell.FormulaR1C1 = "Line 11"
        Range("P2").Select
        ActiveCell.FormulaR1C1 = "Line 12"
        Range("Q2").Select
        ActiveCell.FormulaR1C1 = "Line 13"
        Range("R2").Select
        ActiveCell.FormulaR1C1 = "Line 14"
        Range("S2").Select
        ActiveCell.FormulaR1C1 = "Line 15"
        Range("T2").Select
        ActiveCell.FormulaR1C1 = "Line 16"
    You go uninterupted from Column E to Column T which is Column 5 to Column 20
    You also go uninterupted from Line 1 to Line 16
    So the above snippet (32 lines) could be changed to 3 lines
    For i = 5 To 20
    Cells(2, i).Value = Line " & i - 4
    Next i
    Note: Cells(2, 1) is the same as Range("A2")
    Cells uses Rows first and then columns so the 2 is for Row 2 and the 1 is for Column A (first Column)


    This
    Sheets("Calculations").Select
        Range("B220").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Data for Invoices").Select
        Range("C3:C162").Select
        ActiveSheet.Paste
    should be replaced by something like
    Sheets("Calculations").Range("B220").Copy Sheets("Data for Invoices").Range("C3:C162")
    or
    Sheets("Data for Invoices").Range("C3:C162").Value = Sheets("Calculations").Range("B220").Value    'values only

    Mind you, in the beginning of your code you should use something like
    Dim shCalc As Worksheet, shDatInv As Worksheet
    Set shCalc = Worksheets("Calculations")
    Set shDatInv = Worksheets("Data for Invoices")
    Troughout your procedure (macro) you can now refer to these sheets as (example)
    shCalc.Range("A1").Copy shDatInv.Range("B1")
    instead of the longwinded example a few lines back.


    This will fail if there are empty cells in the intended range.
    Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
    The are multiple better ways but it depends on the circumstances.


    Google something like "How to use variables and why in excel"
    also "How to use With.....End With in Excel macros"


    I certainly would read Post #2 again if I were you.


    If you want to speed up your code, you've got something like 3000 lines of code to work through.
    Happy chasing.

Tags for this Thread

Posting Permissions

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