filters
 

i am trying to create a filter for an invoice that has over 650 items on it,
but i only want it to print the items that i am using for each invoice and
not all the items listed

filters
 

hi ryan

Does your invoice contain 650 items, or does your database or stock list
contain that number. Are you trying to fill in an invoice, drawing from
these items, where at any time you may want to use anything from 1 to ?
items, depending on what is ordered?

If true, you can use =VLOOKUP to get the info into your inovice.
Say in Col A10 down you have Part Numbers. In B you want descriptions, in C
you want unit selling price.

You have a list of items on another sheet, and you have created a range name
for this list, eg stocklist. Said stock list has columns A=Part Number,
B=Description, C=Unit Cost, D=Unit Sell Price

In B10 enter =IF(A10="","",VLOOKUP(A10,stocklist,2,0))
In C10 enter =IF(A10="","",VLOOKUP(A10,stocklist,4,0))

Copy down as far as you want to go.

Enter a part number from the list, and you will get the description in B10
and the selling price in C10.

You can also use data validation to select the part numbers from a dropdown
list, however this will be more complex, as you do not want to scroll through
650 numbers, to select the last one!

--
Hth

Kassie Kasselman
Change xxx to hotmail


"ryan" wrote:

i am trying to create a filter for an invoice that has over 650 items on it,
but i only want it to print the items that i am using for each invoice and
not all the items listed

filters
 

Try this:

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

I forgot where I got this...probably this DG.
Let me know if you have any questions.
Ryan--

--
RyGuy


"ryan" wrote:

i am trying to create a filter for an invoice that has over 650 items on it,
but i only want it to print the items that i am using for each invoice and
not all the items listed