Probably u can do is:
Sheets("Estimates DB").Select
Set ycell = Range("estdbno")
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

coz once i had a similar problem with using sheet("sheetname").range

--
Ak


"Jim Thomlinson" wrote:

Your issue revolves around the sheet reference. Ycell is on sheet Estimates
DB but when you use Range at the beginning of
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

if is the same as
Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name =
"estdbnorng"

which is probably not correct as Estimates DB is probably not the active
sheet. Try this...

with Sheets("Estimates DB")
Set ycell = .Range("estdbno")
.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"
end with

--
HTH...

Jim Thomlinson


"ranswrt" wrote:

The following code worked until I changed how I started it. It originally
started with button on a worksheet (not a control button). I changed it to a
control button now it doesn't work. Everything else in the procedure works
fine. The code is:

Set ycell = Sheets("Estimates DB").Range("estdbno")
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

The error I get is:

Method 'Range" of object '_Worksheet' failed.

Everything is set right. ycell as range, the sheet name and cell name are
correct. I was wondering if it could be because I changed it to a control
button and moved the procedure from a module to to an excel object. Any help
on this would be greatly appreciated.
Thanks