Forum Discussion
Using Microsoft forms to create an invoice in excel.
Hi Suzanne
Using a Pivot Table to generate an Invoice in this case, is not the best solution in my opinion.
In the attached file, which has been saved as a .xlsb as it contains two small macros, I have inserted 2 new sheets called Extract and Invoice.
Extract uses Advanced Filter to extract any given Order Number from your Tale 1 on Sheet 1.
Invoice uses the Transpose() function to turn the Extracted data from Horizontal to Vertical, and then uses Index() , Match() to pull through the price from your table 2 on Blad2 and then multiplies these by quantity to produce the line Totals which get Sumed at the end and have tax added.
Sheet Invoice has some event code which gets triggered only when you change the Order number in cell C2
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Call FilterData
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
If there is a change then the FilterData code in module 1 gets invoked to change the data that Advanced Filter Extracts to sheet Extract, and then used on sheet Invoice.
Sub FilterData()
Sheet1.Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet5.Range("A1:A2"), CopyToRange:=Sheet5.Range("A5:S5"), Unique:=False
End Sub
I hope this helps.
