Pardon me for jumping in...

Add this line:
msgbox "****" & Unit.Offset(0, 1).Value & "****"
Right before this line:
Set sh = Worksheets(Unit.Offset(0, 1).Value)

The asterisks are there just to help you know where the real value starts and
stops.

Now look at what is between those asterisks.

Do you have a worksheet by that name in that workbook?

I'm betting that you have one close, but not quite the same--a rogue space
character in front, at the end or doubled up inside.

Or just a complete typing mistake???


oakman wrote:

Hello Tom,

Thank you for still paying attention to my request for help. I have
entered the midified code. However, I get a different error for the
same line. I get error '9' "Subscript out of range". When I debug,
the line "Set sh = Worksheets(Unit.Offset(0, 1).Value)" is highlited in
yellow. I will be honest with you, I have no idea what this error
means. I REALLY wish that I could be more useful in to your effort to
help me. May I ask one question? Why is it that the macro assigns the
value to the "sh" variable the value of the data one column to the right
of the Unit names? Do you think this could be contributing to the line
of code being so problematic? Please disregard my ignorance if this
question is not relevant. I appreciate the fact that you have helped
me with every problem so far. Please accept my gratitude in your
effort to help me learn.

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

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

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

Dave and Tom,
THANK YOU SO MUCH!
the macro works beautifully!
I am TOTALLY grateful for ALL of your help. I really appreciate the
explanation of how this macro works. With the little that I have been
able to learn, I can only follow the code to a certain point, and then
it stops making sense to me. But now, I have new material to learn
from. You guys have really made my work so much more efficient!

Thank you again!


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