Hi,
I don't understand why I'm getting this error. I have declared all th
variables and I have tried using and not using the Set statement. Coul
use some help.
The error is:
Run Time error 424 Object Required

Here is the Code: I've commented out the error handling to find th
error.

Option Explicit

Sub CreateRoomSheets()

Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range


Set WB = ThisWorkbook
Set SH = WB.Sheets("Room Blank")
Set rng = WB.Sheets("Room List").Range("RoomNo")
Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable")
Set rng1 = WB.Sheets("Room List").Range("RoomName")
Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol")
Set rng3 = WB.Sheets("Room List").Range("RoomRefCol")

'On Error GoTo RET
Application.ScreenUpdating = False


For Each rCell In rng.Cells
Set rng4 = Excel.WorksheetFunction.HLookup(rCell.Offset(0
3).Value, rngNR, 1, False) '<====ERROR
Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1))
rng5.Copy
With rCell
SH.Copy After:=WB.Sheets(WB.Sheets.Count)
With ActiveSheet
.Name = rCell.Value
.Range("B2") = rCell.Value
.Range("B3") = rCell.Offset(0, 1).Value
.Range("B4") = rCell.Offset(0, 2).Value
.Range("A6").PasteSpecial Paste:=xlPasteValues
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End With
Next rCell

'RET:
Application.ScreenUpdating = True
End Su

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=53233