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