You don't need the .offset(0,0) -- but it doesn't hurt either.

Tom's modified code:

Option Explicit

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.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

Looks at b7:b24. Then it copies (row by row) c7:g7, c8:g8, ..., c24:g24 to the
sheet named in column B of that row.

And it pastes it into the first open row in column S of that sheet.

If that isn't what you want, I think it's time to rewrite your problem.



oakman wrote:

Hello Dave,
Your input is appreciated. I have entered the modification to the
code. It turns out that the value coming up between the **** is a piece
of data that I want to copy. In my first worksheet "UHU", the unit
names are in column B from row 6 to 24. From column C to column G, each
Unit has the data that is to be placed in a certain range in the sheet
with the tab that has the unit name. Does that make sense? I dont
think that I explained it correctly the first time. Do you guys think
that because the line of code is referencing a piece of data other than
the unit name, the code might not know what worksheets to look for? I
tried the following line: Set sh = Worksheets(Unit.Offset(0, 0).Value).
The code does not give me any errors, but it is not doing anything
either. For some reason, evethough I changed the offset to stay in the
same column, the msgbox still shows the piece of data next in column C.
We are getting closer and I appreciate it!

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

--

Dave Peterson