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?

It's hard to say without the data, but if the Find fails, then lColTop and
lStartRow don't get set, so could be zero, and I cannot see where lRow gets
set. Cells doesn't like a zero argument.

--
HTH

Bob Phillips

"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?

Bob Phillips wrote:
It's hard to say without the data, but if the Find fails, then lColTop and
lStartRow don't get set, so could be zero, and I cannot see where lRow gets
set. Cells doesn't like a zero argument.

I should have mentioned that the find works, and all variables are
correct. No zeros.

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?

Try changing it to this.

Set rCtySts = wkshtSource.Range(wkshtsource.Cells(lStartRow, lColTop), wkshtSource.Cells(lRow,
lColTop + 1))

You don't have a fully qualified reference with without ID'ing the Worksheet.

HTH
Cal

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.

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.