Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Customer Quoting Database
Hello I have a quoting program in excel that I created, I use this program to quote customers for laser cutting. It is fairly basic, on the first page I input all the data required to determine the amount of the quote, such as the customer, the material, the size of the part, the amount of parts. The second page is a print page, set up to accordingly and drawing all it's information from the 1st page. The third page is a list of all my customers. On the 4th page is my current steel price list. All of this works great so far, but the problem is this: I need to "Record" all the quotes I make. What I would idealy like to have is a different sheet for each customer, so that after I enter the information in the first page that, and select the customer, I could hit a button that would search for that customers "sheet" and copy the information to a new row there. I am not that well versed in excel, so please keep the answers simple. TIA Troy -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#2
|
|||
|
|||
Anybody have any suggestions?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#3
|
|||
|
|||
How about a slightly different alternative?
Add another history sheet--but keep all the history (all the customers) on that one sheet. You can always use data|filter|autofilter (or data|sort) to show/group what you want. And by having all the data in one spot, you can summarize stuff better. It sounds like you could take the data off the first sheet (called "Input"???) and plop it into the "Summary Sheet" via a macro: Option Explicit Sub testme01() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myAddresses As String Dim myCell As Range myAddresses = "A1,F9,A2,B1" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("Summary") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myAddresses) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value myCell.ClearContents 'clean it up??? oCol = oCol + 1 Next myCell End With End Sub I check to see if all the cells have something in them (maybe not required???). I also add the date/time to column A of the summary sheet and the username (taken from Tools|options|General tab) to column B. Then Column C to xxxx go in the same order as the addresses you've specified in this line: myAddresses = "A1,F9,A2,B1" (Change that to match your input worksheet. (mycell.clearcontents may not be necessary, too.) mount_mordor wrote: Hello I have a quoting program in excel that I created, I use this program to quote customers for laser cutting. It is fairly basic, on the first page I input all the data required to determine the amount of the quote, such as the customer, the material, the size of the part, the amount of parts. The second page is a print page, set up to accordingly and drawing all it's information from the 1st page. The third page is a list of all my customers. On the 4th page is my current steel price list. All of this works great so far, but the problem is this: I need to "Record" all the quotes I make. What I would idealy like to have is a different sheet for each customer, so that after I enter the information in the first page that, and select the customer, I could hit a button that would search for that customers "sheet" and copy the information to a new row there. I am not that well versed in excel, so please keep the answers simple. TIA Troy -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 -- Dave Peterson |
#4
|
|||
|
|||
I thought I did.
mount_mordor wrote: Anybody have any suggestions?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 -- Dave Peterson |
#5
|
|||
|
|||
Thankyou for your response I might need a little more help on this macro stuff. I am getting an error that says I cannot run macros, because "macros in this project are disabled..."?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#6
|
|||
|
|||
Close the workbook.
tools|macro|security|security level Change to something that allows macros to run (the options vary between versions of windows). In later versions, you can choose medium that prompts you each time you open a workbook with macros. If you choose this option, just make sure you answer yes to allow macros to run when you open that workbook. Now reopen the workbook with the macro. (This setting isn't retroactive--that's why you have to close and reopen the workbook.) mount_mordor wrote: Thankyou for your response I might need a little more help on this macro stuff. I am getting an error that says I cannot run macros, because "macros in this project are disabled..."?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 -- Dave Peterson |
#7
|
|||
|
|||
Ah it was the rebooting excel that I missed. thanks Please see my attached workbook. I was getting the messagebox "Please fill in all the cells" Thanks +-------------------------------------------------------------------+ |Filename: LASER WORKSHEET1.zip | |Download: http://www.excelforum.com/attachment.php?postid=3775 | +-------------------------------------------------------------------+ -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#8
|
|||
|
|||
I don't see the attachment and I don't open attachments anyway.
But if you don't want that check, just delete these lines: If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If mount_mordor wrote: Ah it was the rebooting excel that I missed. thanks Please see my attached workbook. I was getting the messagebox "Please fill in all the cells" Thanks +-------------------------------------------------------------------+ |Filename: LASER WORKSHEET1.zip | |Download: http://www.excelforum.com/attachment.php?postid=3775 | +-------------------------------------------------------------------+ -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 -- Dave Peterson |
#9
|
|||
|
|||
Well I can see the attachment, but here is a screenshot of my first page +-------------------------------------------------------------------+ |Filename: excel pic1.zip | |Download: http://www.excelforum.com/attachment.php?postid=3776 | +-------------------------------------------------------------------+ -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#10
|
|||
|
|||
Ok I fiqured out the code, there was a "End With" missing at the end. So that works great, but it's not exactly what I am looking for, although it's getting me closer. I want a separate page for each customer, so after my 4th worksheet, I want to start in alphabetical order all my customers. Then when I run this code it will search by customer, and find "their" worksheet, and enter the data under the last row. Is this possible?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
#11
|
|||
|
|||
It's possible, but I still wouldn't do it. Separating the data is never a good
idea (in my opinion). I'd still build that giant summary worksheet. If you want to split that summary worksheet later, you may find something close at: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb or Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb or maybe Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ========== And it sounds like you modified the code (all the "end with"'s were there before). You could use: Set historyWks = Worksheets(inputWks.range("a1").value) This assumes that each of the customer worksheets already exist and you put the customer name in A1 of the inputworksheet. mount_mordor wrote: Ok I fiqured out the code, there was a "End With" missing at the end. So that works great, but it's not exactly what I am looking for, although it's getting me closer. I want a separate page for each customer, so after my 4th worksheet, I want to start in alphabetical order all my customers. Then when I run this code it will search by customer, and find "their" worksheet, and enter the data under the last row. Is this possible?? -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 -- Dave Peterson |
#12
|
|||
|
|||
Ok lets say I go with keeping all my data in one huge sheet. What is the easiest way to get all rows of data from my first sheet into my customer quote database sheet. Right now it is just grabbing the first line, but I sometimes enter more than one quote in the first page, so I may have up to 20 lines. Right now in the code I have ----myAddress = "C6,B10,C10,D10,E10,F10,G10,H0,I10,J10,K10"----- Do I have to enter each row in, so I would basically have 20times the numbers to enter, or is there a string I could enter??? I need to go from B10:K10 to B34:K34 Thanks -- mount_mordor ------------------------------------------------------------------------ mount_mordor's Profile: http://www.excelforum.com/member.php...o&userid=26879 View this thread: http://www.excelforum.com/showthread...hreadid=401134 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links to External Database | Excel Discussion (Misc queries) | |||
How do I create a customer database in excel | Excel Discussion (Misc queries) | |||
Text manipulaion (summarizing customer records) Index function probably not good choice | Excel Discussion (Misc queries) | |||
How can I use Excel as a simple database for Quoting? | Excel Discussion (Misc queries) | |||
Searchable Database | Excel Worksheet Functions |