I have created a database within excel. The standard database available in excel is too small and the users do not have access to Access. I have used cell referencing and then simply copied the row of references down a row, etc, etc.

I then use a series of application.vlookups to create variables and then place the variables back into the cell they originally came from. See the code below.

I know there must be a simpler way of doing this but not sure how. Anyone????

This is the code to save quote data.

Sub ToDbNoPrint()
Application.ScreenUpdating = False
' unprotects the database before saving
Sheets("database").unprotect Password:="cpi2006"

Sheets("database").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Selection.copy
Rows("5:5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("b5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[1]C+1"
Range("b6").Select
ActiveWorkbook.Save
Sheets("Schedule").Select
Range("H3").Select

' protects the database
Sheets("database").protect Password:="cpi2006", DrawingObjects:=False, Contents:=True, Scenarios:= _
False
Application.ScreenUpdating = True
End Sub

And, this is the reviewing of the quote. I'm embarressed at my lack of knowledge but it does work!!

Sub renewal()
Application.ScreenUpdating = False

' Reviews quotes from the database sheet

' The quote number is entered and then we lookup
quote_no = InputBox("Please Enter the Policy or Quote Number", vbOK)

' Client Number
Sheets("database").Select
Pol_No = Application.VLookup(quote_no, Range("rawdata"), Range("data1"), False)

If IsError(Pol_No) Then
MsgBox "This policy/quote number does not exist. Please check the database page for a correct reference number."
Sheets("Information").Select
Exit Sub
End If

' Information page
Auth_Level = Application.VLookup(quote_no, Range("rawdata"), Range("data3"), False)
user = Application.VLookup(quote_no, Range("rawdata"), Range("data4"), False)
Name = Application.VLookup(quote_no, Range("rawdata"), Range("data5"), False)
Street = Application.VLookup(quote_no, Range("rawdata"), Range("data6"), False)
Town = Application.VLookup(quote_no, Range("rawdata"), Range("data7"), False)
broker = Application.VLookup(quote_no, Range("rawdata"), Range("data8"), False)
attn = Application.VLookup(quote_no, Range("rawdata"), Range("data9"), False)
Fax = Application.VLookup(quote_no, Range("rawdata"), Range("data10"), False)
email = Application.VLookup(quote_no, Range("rawdata"), Range("data11"), False)
ind_code = Application.VLookup(quote_no, Range("rawdata"), Range("data12"), False)
keyword1 = Application.VLookup(quote_no, Range("rawdata"), Range("data13"), False)
Source = Application.VLookup(quote_no, Range("rawdata"), Range("data14"), False)
reason1 = Application.VLookup(quote_no, Range("rawdata"), Range("data15"), False)
reason2 = Application.VLookup(quote_no, Range("rawdata"), Range("data16"), False)
reason3 = Application.VLookup(quote_no, Range("rawdata"), Range("data17"), False)
reason4 = Application.VLookup(quote_no, Range("rawdata"), Range("data18"), False)
post_code = Application.VLookup(quote_no, Range("rawdata"), Range("data19"), False)
keyword2 = Application.VLookup(quote_no, Range("rawdata"), Range("data20"), False)
Date1 = Application.VLookup(quote_no, Range("rawdata"), Range("data21"), False)
Date2 = Application.VLookup(quote_no, Range("rawdata"), Range("data22"), False)
newbus = Application.VLookup(quote_no, Range("rawdata"), Range("data23"), False)
bd = Application.VLookup(quote_no, Range("rawdata"), Range("data24"), False)

'Material damage
fxs = Application.VLookup(quote_no, Range("rawdata"), Range("data25"), False)
BLDval = Application.VLookup(quote_no, Range("rawdata"), Range("data26"), False)
bldcvr = Application.VLookup(quote_no, Range("rawdata"), Range("data27"), False)
SI1 = Application.VLookup(quote_no, Range("rawdata"), Range("data28"), False)
SI2 = Application.VLookup(quote_no, Range("rawdata"), Range("data29"), False)
SI3 = Application.VLookup(quote_no, Range("rawdata"), Range("data30"), False)
SI4 = Application.VLookup(quote_no, Range("rawdata"), Range("data31"), False)
SI5 = Application.VLookup(quote_no, Range("rawdata"), Range("data32"), False)
cntcvr = Application.VLookup(quote_no, Range("rawdata"), Range("data33"), False)
SI6 = Application.VLookup(quote_no, Range("rawdata"), Range("data34"), False)
SI7 = Application.VLookup(quote_no, Range("rawdata"), Range("data35"), False)
Stock = Application.VLookup(quote_no, Range("rawdata"), Range("data36"), False)
Start = Application.VLookup(quote_no, Range("rawdata"), Range("data37"), False)
term = Application.VLookup(quote_no, Range("rawdata"), Range("data38"), False)
Constn = Application.VLookup(quote_no, Range("rawdata"), Range("data39"), False)
Constn2 = Application.VLookup(quote_no, Range("rawdata"), Range("data40"), False)
sandwich = Application.VLookup(quote_no, Range("rawdata"), Range("data41"), False)
Water = Application.VLookup(quote_no, Range("rawdata"), Range("data42"), False)
Sprink = Application.VLookup(quote_no, Range("rawdata"), Range("data43"), False)
grossprofit = Application.VLookup(quote_no, Range("rawdata"), Range("data44"), False)
indem = Application.VLookup(quote_no, Range("rawdata"), Range("data45"), False)
clmsexp = Application.VLookup(quote_no, Range("rawdata"), Range("data46"), False)
icow = Application.VLookup(quote_no, Range("rawdata"), Range("data47"), False)
accounts = Application.VLookup(quote_no, Range("rawdata"), Range("data48"), False)
loss = Application.VLookup(quote_no, Range("rawdata"), Range("data49"), False)

' Windows("Business Protection V3.0.xls").Activate
With Worksheets("Information")
.Range("i7") = Auth_Level
.Range("e9") = user
.Range("b13") = Name
.Range("b15") = Street
.Range("b17") = Town
.Range("b19") = broker
.Range("b21") = attn
.Range("b23") = Fax
.Range("b25") = email
.Range("g27") = ind_code
.Range("a71") = Source
.Range("a34") = reason1
.Range("a35") = reason2
.Range("a36") = reason3
.Range("a37") = reason4
.Range("g39") = post_code
.Range("g43") = Date1
.Range("g45") = Date2
.Range("g49") = newbus
.Range("g51") = bd
.Range("g47") = Pol_No
End With
Worksheets("keywords").Range("d1") = keyword1
Worksheets("keywords").Range("d37") = keyword2

' material damage
With Worksheets("Material Damage")
.Range("md_xs") = fxs
.Range("val") = BLDval
.Range("cover") = bldcvr
.Range("f23") = SI1
.Range("f25") = SI2
.Range("f27") = SI3
.Range("f29") = SI4
.Range("f31") = SI5
.Range("cover2") = cntcvr
.Range("f39") = SI6
.Range("f41") = SI7
.Range("f45") = Stock
.Range("f51") = Start
.Range("f53") = term
.Range("wall_con") = Constn
.Range("flr_con") = Constn2
.Range("f61") = sandwich
.Range("water") = Water
.Range("sprink") = Sprink
.Range("f73") = grossprofit
.Range("indem") = indem
.Range("f77") = clmsexp
.Range("f79") = icow
.Range("f81") = accounts
.Range("f83") = loss
End With

end sub