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

[chiwidan]

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

[SamT]

Create an invoice Template with the table and formulas.

For each Invoice Name, copy the Template and rename it, then fill in the blanks

[chiwidan]

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…

https://www.mrexcel.com/board/attachments/1626559661770-png.42996/

[Paul_Hossler]

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 ...."

[chiwidan]

Perfect... I have added the file now... I believe

[jolivanes]

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

[snb]

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.

[chiwidan]

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

[chiwidan]

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

[chiwidan]

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

[chiwidan]

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

[chiwidan]

Hi everyone,

Apologies for the crosspost. Just learning.

I have also asked this at:

https://www.ozgrid.com/forum/index.php?thread/1229957-to-automatically-create-a-variable-number-of-worksheets-name-them-and-put-partic/&postID=1251278#post1251278

and

https://www.mrexcel.com/board/threads/to-automatically-create-a-variable-number-of-worksheets-name-them-and-the-enter-the-same-formula-s-in-all-of-them.1175635

[Paul_Hossler]

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

[chiwidan]

thanks Paul, I will get onto it...

[Paul_Hossler]

All part of the learning process:yes

[jolivanes]

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.