It still hangs on the second file name. Here is the exact code which is in
the first file.....

Private Sub Workbook_Open()
Dim rng As Range, sNum As String, s As String
Dim rng1 As Range, rng2 As Range
With Workbooks("Automation Quote-revising KENTS.xls").Worksheets("Quotation")
Set rng1 = .Range("F1")
Set rng2 = .Range("C10")
End With
With Workbooks("Quote Reference Numbers.xls").Worksheets("Sheet1")
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) & sNum
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?


End Sub



"Toppers" wrote:

With the change I suggested, your code worked for me. I know it's obvious but
check your worksheet names for leading/trailing blanks. And which statement
does it error on?

"Theresa" wrote:

No.....didn't work....still get the same error. Do you know any other way to
accomplish this?

"Toppers" wrote:



Try:

With WorkBooks("Automation Quote").Worksheets("Sheet1")


With Workbooks("Quote Reference Numbers"). _

"Theresa" wrote:

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