Normally, the default attribute of a range object is Value, but in that
location, it appears to be troublesome, so try this:

Sub Unit_Hourly_Updates()
Dim Unit As Range, rng as Range
Dim sh as Worksheet
For Each Unit In Sheets("UHU").Range("B7:B24")
' next line modified
set sh = Worksheets(Unit.offset(0,1).Value)
set rng = sh.cells(33,"S").End(xlup)
if rng.row < 9 then
set rng = sh.cells(9,"S")
else
if not isempty(rng) then
set rng = rng(2)
end if
end if
unit.offset(0,1).Resize(1,5).copy rng
Next Unit
End Sub

--
Regards,
Tom Ogilvy

"oakman" wrote in
message ...

Greetings Tom,
I would like to thank you for your replies. They are very helpful. I
also ask for a little more of your help if possible. When I place the
new code in the module, I get a Run-time Error '13' "Type mismatch" at
the same point as before. When I debug, the line "Set
sh=Worksheets(Unit.Offset(0,1))" is highlighted. I have tried the
following to no avail "Set sh = Worksheets(Unit).Value". I figured that
since you are helping me I should at least make an effort. Again, I
would like to tell you how appreciative I am for any help that you are
able to provide!


--
oakman
------------------------------------------------------------------------
oakman's Profile:
http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=525659