Macro Problem while saving a quote

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Pete, a couple of suggestions.
I believe if your list was growing by adding to the bottom instead of the top, your ComboBox selections would be on the proper order.
Also, because you’re only pasting the values, your entire routine can be written a lot shorter (and more efficiently) if done like so… (The macro recorder’s great in a lot of cases, but it’s not the most efficient of code.)
Code:
Sub Test()
'Quote Number
Sheets("Saved Quote Details").Range("A65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E16").Value

'Customer Number
Sheets("Saved Quote Details").Range("B65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E13").Value

'Date
Sheets("Saved Quote Details").Range("C65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G10").Value

'CPU
Sheets("Saved Quote Details").Range("D65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E25").Value

'CPU Price
Sheets("Saved Quote Details").Range("E65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G25").Value

'Motherboard
Sheets("Saved Quote Details").Range("F65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E26").Value

'Motherboard Price
Sheets("Saved Quote Details").Range("G65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G26").Value

'RAM
Sheets("Saved Quote Details").Range("H65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E27").Value

'RAM Price
Sheets("Saved Quote Details").Range("I65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G27").Value

'Case
Sheets("Saved Quote Details").Range("J65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E28").Value

'Case Price
Sheets("Saved Quote Details").Range("K65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G28").Value

'Graphics Card
Sheets("Saved Quote Details").Range("L65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E29").Value

'Graphics Card Price
Sheets("Saved Quote Details").Range("M65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G29").Value

'Hard Drive
Sheets("Saved Quote Details").Range("N65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E30").Value

'Hard Drive Price
Sheets("Saved Quote Details").Range("O65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G30").Value

'CD/CD-RW/DVD/DVDRW
Sheets("Saved Quote Details").Range("P65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E31").Value

'CD/CD-RW/DVD/DVDRW Price
Sheets("Saved Quote Details").Range("Q65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G31").Value

'CD/CD-RW/DVD/DVDRW1
Sheets("Saved Quote Details").Range("R65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E32").Value

'CD/CD-RW/DVD/DVDRW1 Price
Sheets("Saved Quote Details").Range("S65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G32").Value

'Monitor
Sheets("Saved Quote Details").Range("T65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E33").Value

'Monitor Price
Sheets("Saved Quote Details").Range("U65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G33").Value

'Keyboard and Mouse
Sheets("Saved Quote Details").Range("V65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E34").Value

'Keyboard and Mouse Price
Sheets("Saved Quote Details").Range("W65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G34").Value

'Operating System
Sheets("Saved Quote Details").Range("X65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E35").Value

'Operating System Price
Sheets("Saved Quote Details").Range("Y65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G35").Value

'Speakers
Sheets("Saved Quote Details").Range("Z65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E36").Value

'Speakers Price
Sheets("Saved Quote Details").Range("AA65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G36").Value

'Extra
Sheets("Saved Quote Details").Range("AB65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("E37").Value

'Extra Price
Sheets("Saved Quote Details").Range("AC65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G37").Value

'Sub Total
Sheets("Saved Quote Details").Range("AD65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G39").Value

'VAT
Sheets("Saved Quote Details").Range("AE65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G41").Value

'Delivery
Sheets("Saved Quote Details").Range("AF65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("T4").Value

'Discount
Sheets("Saved Quote Details").Range("AG65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G47").Value

'Quantity
Sheets("Saved Quote Details").Range("AH65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G49").Value

'Grand Total
Sheets("Saved Quote Details").Range("AI65536").End(xlUp)(2, 1).Value = _
    Sheets("Quote").Range("G51").Value
End Sub
This will put each new set of data in the next line down in the Saved Quotes Sheet instead of at the top.
If this causes a problem with your combobox, post back and we can address that too.

Hope it helps,
Dan

Edit:
If you still want to end up in the Saved Quotes Details sheet when the code finishes, simply add this to the bottom, right above the End Sub statement.
Code:
Sheets("Saved Quote Details").Select
 

wwfc_barmy_army

New Member
Joined
Jan 30, 2005
Messages
13
Thanks, thats perfect. I've tested it and it seems to work fine! If i do have any problems i'll be back ;)

Thanks alot mate!!! :pray:

Pete.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
HalfAces sol'n may have one problem... it assumes that there will be a value for each Quote box. If a value is missing you may find that future data will end up on the wrong row.
The following sol'n determines the row to use once so that all data falls on the same row ... :wink:

Sub CopyData()
Dim QuoteRng As Range
For Each Qvar In Sheets("Quote").Range("E16,E13,G10,E25,G25,E26,G26,E27,G27,E28,G28,E29," _
& "G29,E30,G30,E31,G31,E32,G32,E33,G33,G10,G34,E35,G35,E36,G36,E37,G37,G39,G41,T4,G47,G49,G51")
With Sheets("Saved Quote Details")
Col = Col + 1
If Col = 1 Then
BotRw = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(BotRw, Col).Value = "-"
End If
.Cells(BotRw, Col).Value = Qvar.Value
End With
Next Qvar
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yes, I didn't account for any quote boxes being left blank.
Extremely nice Nimrod!

I always like it when your post follows mine. I get to see how I did it, and then how it could be (should've been! :biggrin: ) done. This is better for me than simply seeing the better way right up front. I can make direct correlations from what I did to how you did it and this makes it easier for me to learn better methods.

I hope you know how much I respect & appreciate your continued tutelage.
I sincerely hope it doesn't stop.

(y)
Dan
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello HalfAce: You make me blush :oops: .

Thanks for being so open to my input, but I certainly consider you as important to my education as you seem to think I am for your's. Your posts have instructed me on different and better methods on many occasions .... thx for your contributions and attitude. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top