defining a range

[dhgerstman]

In the following code below, I want to copy a number of items from
Worksheet "Items" to Worksheet "Rooms" based on the value of txtRoom.

What am I doing wrong when I set "listrange" in the select statement?

I get a "Method 'range' of object '_global' error"

Thanks,

David

Worksheets("Rooms").Activate
ActiveSheet.Range("A8:A17").Select
Selection.ClearContents
Selection.Rows.RowHeight = 36

Worksheets("Items").Activate
Set itemrange = Worksheets("Items").Range("a1",
Range("a1").End(xlToRight))
For Each ir In itemrange
If ir.Text = txtRoom Then
Exit For
End If
Next ir
Worksheets("Items").Activate
Select Case txtRoom

Case "Room1"

Set listrange = Worksheets("Items").Range(ir, Range(ir).End(xlDown))
listrange.Copy Destination:=Worksheets("SF 701").Range("a8")

Case "Room2"
....

Case "Room3"
....

Case "Room4"
....

End Select

 

[Doug Robbins - Word MVP]

The group to which you have posted your question is for the use of Visual
Basic with the Word application. You are more likely to get help for the
use of Visual Basic with Excel if you post to the
microsoft.public.excel.programming newsgroup.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[dhgerstman]

The group to which you have posted your question is for the use of Visual
Basic with the Word application.  You are more likely to get help for the
use of Visual Basic with Excel if you post to the
microsoft.public.excel.programming newsgroup.

Whoops sorry. The Google interface didn't show the complete group
name.

I will repost and be more careful in the future, thanks for letting me
know.

David