For Each Loop not working properly

[oakman]

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

[Tom Ogilvy]

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

[oakman]

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

[Tom Ogilvy]

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

[oakman]

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!

[oakman]

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

[Tom Ogilvy]

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

[oakman]

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!

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[oakman]

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.

[Dave Peterson]

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

[oakman]

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!

[Dave Peterson]

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

[oakman]

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!