Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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 +---------------------------------------------------------------- ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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. -- Regards, Tom Ogilvy Laura90210 wrote in message ... 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 spare and could have a quick look for me that would really make my day. Laura x +----------------------------------------------------------------+ | Attachment filename: watkins & powis price list.xls | |Download attachment: http://www.excelforum.com/attachment.php?postid=355302| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help. Please read...
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(Cel ls(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 Laura90210 wrote in message ... 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 spare and could have a quick look for me that would really make my day. Laura x +----------------------------------------------------------------+ | Attachment filename: watkins & powis price list.xls | |Download attachment: http://www.excelforum.com/attachment.php?postid=355302| +----------------------------------------------------------------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Read Only If Statement | Excel Discussion (Misc queries) | |||
Help getting macro to work in read only | Excel Discussion (Misc queries) | |||
Cant find Macro in read only | Excel Discussion (Misc queries) | |||
Force read-only in auto_open macro | Excel Discussion (Misc queries) | |||
macro to read from Project | Excel Programming |