Hi:

I have two work books. One contains a template for a quote the other will
contain only a quote #, date of the quote and customer name. When I open the
quote template I want to have the quote # field look into the other file,
find the last quote # used and increment by 1. When the quote template is
saved or once completed, I want the quote #, date, and customer name to be
written back to the other file. I am using the following but keep getting a
"Subscript out of range" error.

Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1,
the company name in B1

Dim rng as Range, sNum as String, s as String
Dim rng1 as Range, rng2 as Range
With WorkBooks("Automation Quote.xls").Worksheets("Sheet1")
set rng1 = .Range("A1")
set rng2 = .Range("B1")
End With
With Workbooks("Quote Reference Numbers.xls"). _
Worksheets("Data")
set rng = .Cells(rows.count,1).End(xlup)
End With
snum = format(clng(right(rng.value,4))+1,"0000")
s = left(rng.value,len(rng.value)-4) & s
set rng = rng.offset(1,0)
rng.value = s
rng.offset(0,1).Value = Date
rng.offset(0,1).Numberformat = "mmm d, yyyy"
rng.offset(0,2).Value = rng2
' rng1.value = s ' update Automation Quote with new number?

What is wrong?

Any help would be appreciated.

Thanks,

Theresa