Macro Help. Please read...

[Laura90210]

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 come
along to create a quotation and puts in some quantities of variou
products, a button perhaps is pressed and that selects all the item
that have a quantity against them and takes the Part Number
Description and Price of those items onto another page in th
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 ar
about 150 items)

So I need the macro to take all the rows with quantities and leave al
the rows that no quantity was specified. To create like a quote/reciep
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, bu
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 (e-mail address removed)

 

[Don Guillett]

Time to learn how to record a macro while you use
data>filter>autofilter>copy>select other sheet>paste

 

[Tom Ogilvy]

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.

 

[Laura90210]

I dont understand....


The macro is dependant on what the user enters.


I really dont know what to do...


Tried what you said Don, I didnt get what you meant.


The information I want on the second sheet will be different everytim
- ie. entering different quantities of different products

 

[Laura90210]

Tom, thanks for your reply.

I tried that and I got a debugger....

It sounds very complicated and brilliant though.....


I have attached my price list to this. If you had 5 minutes to spar
and could have a quick look for me that would really make my day.

Laura

+----------------------------------------------------------------
| Attachment filename: watkins & powis price list.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=355302
+----------------------------------------------------------------

 

[Tom Ogilvy]

Sub Button1_click()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim rng4 As Range
With Sheets("Sheet1")
Set rng4 = .Range(.Range("A4"), _
.Cells(Rows.Count, 1).End(xlUp)).Resize(, 5)
rng4.AutoFilter Field:=5, Criteria1:="<>"
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
Worksheets("sheet2").UsedRange.Columns.AutoFit
End Sub

worked for me with your workbook. Make sure the code is in a general
module, not a sheet module.

 

[Zbiq]

Pls try with this simple sub.

Sub MACRO()
Sheets("Sheet1").Select
Rows("4:4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("a2").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Range("e5").Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If ActiveCell.Value <> "" Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
ActiveCell.Offset(0, 4).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Sheet2").Select
a = ActiveCell.Row
b = Application.WorksheetFunction.SumProduct(Range(Cells(a, 4),
Cells(3, 4)), Range(Cells(a, 5), Cells(3, 5)))
ActiveCell.Offset(0, 3).Value = "Total: $" & b
c = Application.WorksheetFunction.Sum(Range(Cells(a, 5), Cells(3, 5)))
ActiveCell.Offset(0, 4).Value = c & " of items"
Columns("C:C").Delete Shift:=xlToLeft
End Sub