Greetings to all,
I would like to thank people in advance for any help that may be given
I am very grateful! Here is my problem: I have a list of units wit
data next to them. So far, all that I am trying to do is to go throug
each of these units and select a range of data next to it. I can ge
the macro to select the range of data for the first unit. The loo
should go to the next unit and copy the same range of data as the on
above. However, this does not happen at all. Any help is REALL
appreciated! Thank you

Sub Unit_Hourly_Updates()
Dim Unit As Variant
Unit = Sheets("UHU").Range("B7:B24")
For Each Unit In Sheets("UHU").Range("B7:B24")
Sheets("UHU").Activate
Range("C7:G7").Select
Next Unit
End Su

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=52565

You gave no indication in your original code where to copy the data, so
This copies the data to the next sheet after UHU a line at a time (make sure
UHU isn't the last sheet in the tab order and one after it is blank).
Sub Unit_Hourly_Updates()
Dim Unit As Range
Sheet("UHU").Next.Activate
Range("A1").Select
For Each Unit In Sheets("UHU").Range("B7:B24")
Sheets("UHU").Activate
unit.offset(0,1).Resize(1,5).Select
' msgbox "Selection is: " & selection.Address
selection.copy
sheets("UHU").Next.Activate
Sheets("UHU").Paste
ActiveCell.Offset(1,0).Select
Next Unit
End Sub

the alternative off course
Sub copyDate()

Sheets("UHU").Range("B7:B24").offset(0,1).Resize(, 5).Copy _
Destination:= Sheets("UHU").Next.Range("A1")
End sub


--
Regards,
Tom Ogilvy


"oakman" wrote:


Greetings to all,
I would like to thank people in advance for any help that may be given.
I am very grateful! Here is my problem: I have a list of units with
data next to them. So far, all that I am trying to do is to go through
each of these units and select a range of data next to it. I can get
the macro to select the range of data for the first unit. The loop
should go to the next unit and copy the same range of data as the one
above. However, this does not happen at all. Any help is REALLY
appreciated! Thank you

Sub Unit_Hourly_Updates()
Dim Unit As Variant
Unit = Sheets("UHU").Range("B7:B24")
For Each Unit In Sheets("UHU").Range("B7:B24")
Sheets("UHU").Activate
Range("C7:G7").Select
Next Unit
End Sub


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

Thank you Tom,
I actually have a multitude of other sheets with each tab named afte
the list of units in the UHU sheet. Ideally, the macro would take th
data for each unit in the UHU sheet and paste it at a destination i
the appropriate sheet for the unit in question. I could not get th
loop to go through each unit to work, so I figured that it would b
best to get this working first before moving on. If you can help m
with the second part of the macro (getting the data from the uni
listed in the UHU sheet to the sheet with that name), I would b
extremely grateful! Here some of the code that I have so far for th
second part:
Units_Sht_Name = Sheets("UHU").Range("c7:c24").Value
With Worksheets
Worksheets(Units_Sht_Name).Activate
Range("O9").Select
ActiveCell.Offset(0, 1).Activate
Range("O9:S32").Select
Selection.PasteSpecial
End With

Thank you so muc

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=52565

Sub Unit_Hourly_Updates()
Dim Unit As Range, rng as Range

For Each Unit In Sheets("UHU").Range("B7:B24")
set sh = Worksheets(Unit.offset(0,1))
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:


Thank you Tom,
I actually have a multitude of other sheets with each tab named after
the list of units in the UHU sheet. Ideally, the macro would take the
data for each unit in the UHU sheet and paste it at a destination in
the appropriate sheet for the unit in question. I could not get the
loop to go through each unit to work, so I figured that it would be
best to get this working first before moving on. If you can help me
with the second part of the macro (getting the data from the unit
listed in the UHU sheet to the sheet with that name), I would be
extremely grateful! Here some of the code that I have so far for the
second part:
Units_Sht_Name = Sheets("UHU").Range("c7:c24").Value
With Worksheets
Worksheets(Units_Sht_Name).Activate
Range("O9").Select
ActiveCell.Offset(0, 1).Activate
Range("O9:S32").Select
Selection.PasteSpecial
End With

Thank you so much


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

Thank you very much Tom,

I appreciate your reply.
I will give this a try.
I will also try to understand it so that I can learn

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=52565

Hello Tom,
I would like to stress how much I appreciate the help that I have bee
getting so far. However, when I tried the code above, I got a 'Typ
Mismatch' error. Upon debugging, the line "Se
sh=Worksheets(Unit.Offset(0,1))" was highlighted in yellow. I tried t
Dim the "sh" as variat to no avail.
I vaguely understand kind of what might be going on, but trust me, I a
no where close to really getting it. Can I ask for a little more of you
assistance please?

Thank yo

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=52565

Sub Unit_Hourly_Updates()
Dim Unit As Range, rng as Range
Dim sh as Worksheet
For Each Unit In Sheets("UHU").Range("B7:B24")
set sh = Worksheets(Unit.offset(0,1))
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:


Hello Tom,
I would like to stress how much I appreciate the help that I have been
getting so far. However, when I tried the code above, I got a 'Type
Mismatch' error. Upon debugging, the line "Set
sh=Worksheets(Unit.Offset(0,1))" was highlighted in yellow. I tried to
Dim the "sh" as variat to no avail.
I vaguely understand kind of what might be going on, but trust me, I am
no where close to really getting it. Can I ask for a little more of your
assistance please?

Thank you


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

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

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

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

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