Sub Button1_click()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim rng4 as Range
With Sheets("Sheet1")
set rng4 = .Range(.Range("A1"),.Range("A3").currentRegion)
rng4.CurrentRegion.AutoFilter Field:=5, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng1 = rng2.Columns(1).SpecialCells(xlVisible)
If rng1.Count 1 Then
Set rng3 = Intersect(rng2.EntireRow, .Columns("A:F"))
rng3.Copy _
Destination:=Sheets("Sheet2").Range("A1")
Else
MsgBox "No visible rows"
End If
rng2.AutoFilter
End With
Worksheets("sheet2").Columns(3).Delete
End Sub

Drag a button from the forms toolbar and place it on the sheet. Assign the
above macro to it.


--
Regards,
Tom Ogilvy



Laura90210 wrote in message
...

I have a price list on excel, it comprises of the columns:

Part Number
Description
Cost (What the business pays)
Price (What the business charges)
Quantity

I need however to make some sort of macro so that when a user comes
along to create a quotation and puts in some quantities of various
products, a button perhaps is pressed and that selects all the items
that have a quantity against them and takes the Part Number,
Description and Price of those items onto another page in the
workbook.

For instance. (Sheet one)

Part No......Description....Cost....Price....Quantity

45854.......Sweep Tee......2.5.......3.5.........1
45855.......Pipe Bend........1.5.......4.5.........3
45856.......Galv Pipe.........1.0.......1.5
45857.......Galv Socket.....5.0.......6.5
45859.......Medium Tube...4.5......6.0..........4
45860.......Hollow Plug.....1.75....2.5
45861.......M/F Bend........2.0......4.5..........2
45862.......F Elbow..........2.5......3.5
45863.......Galv Cap........3.5......5.0...........3


Then perhaps a button with a macro attached to it.

Which, when pressed, creates this on another sheet. (Sheet 2)

Part No....Description.......Price.....Quantity

45854......Sweep Tee.........3.5...........1
45855......Pipe Bend...........4.5...........3
45859......Medium Tube.......6.0...........4
45861......M/F Bend............4.5...........2
45863......Galv Cap............5.0...........3

.........................Total:.....£23.5......13 Items


(Note: This is not the actual table I need to do this with, there are
about 150 items)

So I need the macro to take all the rows with quantities and leave all
the rows that no quantity was specified. To create like a quote/reciept
kind of document.

It looks oh so simple, but this really is haunting me. :(

I would be very grateful if you can help me with this, no pressure, but
my job is on the line! lol (No really, it is)

Any help would be wonderful.

Thanks for your time.

Laura x

(My email address is )


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/