It is still not working, it is currently erroring on the first file name. If
I add the file extension, it gets to the second file, but always errors
there. I have checked the file name, etc. Do I have to have the second file
open already? I would not have a problem sending you the files, if that is
acceptable to you.

"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