Glad to help.

I thought that having wkshtSource.range told it where
the cells were.

Whenever you get "Error 1004 'Method x of y failed'", double check your
assumptions. I find they are usually the culprit. :-)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"davegb" wrote in message
ups.com...

George Nicholson wrote:
Set rCtySts = wkshtSource.Range(Cells(lStartRow, lColTop), Cells(lRow,
lColTop + 1))<------ ERROR

where is Cells? what worksheet? If you provide that info the error will
probably will go away. As it stands Excel doesn't know what you want
(which
is usually the case with that error message), so you need to give it more
info. In this case verbosity is your friend.

Something like:
wkshtSource.Range(wkshtSource.Cells(lStartRow, lColTop),
wkshtSource.Cells(lRow, lColTop + 1))

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"davegb" wrote in message
oups.com...
I'm getting a "method range of object worksheet failed" error at the
line marked:

Set rStart = Range("B4")
lRow = rStart.End(xlDown).Row - 1

Set rCty = Range(rStart, Cells(lRow, "B"))


Call ColUnHideAll

Set wkshtSource = ActiveSheet

Set wkshtNew = Workbooks.Add.Sheets("sheet1")

rCty.Copy Destination:= _
wkshtNew.Range("A2")

Set rTopCell = wkshtSource.Range("3:3").Find("top", LookIn:=xlValues,
lookat:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column
lStartRow = rTopCell.Row + 1
End If

Set rCtySts = wkshtSource.Range(Cells(lStartRow, lColTop), Cells(lRow,
lColTop + 1))<------ERROR

rCtySts.Copy Destination:=wkshtNew.Range("B2")

End Sub

A couple of lines above, method range of the same worksheet worked
fine. So what's wrong with having another range of the same worksheet?


Thanks, George! I thought that having wkshtSource.range told it where
the cells were.