Worksheet loop won't loop

[L. Howard Kittle]

Hello Excell Experts and Users,

When I run this code, it runs 10 times on the first sheet. I have 10 sheets
in the workbook. Tried it on three different workbooks, in a module, in the
ThisWorkbook module and in the first sheet module.

This is pretty basic but it has me baffled...?

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

Range("IV4").End(xlToLeft).Offset(0, 1).Value = _
Range("IV4").End(xlToLeft).Value + 1
Range("IV13").End(xlToLeft).Resize(6, 1).Copy _
Range("IV13").End(xlToLeft).Offset(0, 1)

Next I
End Sub

Thanks for any help.
Regards,
Howard

 

[Guest]

Give this a wirl...

Sub WorksheetLoop()
Dim wks as worksheet

For each wks in worksheets
with wks
.Range("IV4").End(xlToLeft).Offset(0, 1).Value = _
.Range("IV4").End(xlToLeft).Value + 1
.Range("IV13").End(xlToLeft).Resize(6, 1).Copy _
.Range("IV13").End(xlToLeft).Offset(0, 1)
end with
Next wks
End Sub

With your code you would need to reference each sheet something like this...

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
with sheets(I)
.Range("IV4").End(xlToLeft).Offset(0, 1).Value = _
.Range("IV4").End(xlToLeft).Value + 1
.Range("IV13").End(xlToLeft).Resize(6, 1).Copy _
.Range("IV13").End(xlToLeft).Offset(0, 1)
end with
Next I
End Sub

 

[Tom Ogilvy]

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
' you left this out
Worksheets(i).Activate
Range("IV4").End(xlToLeft).Offset(0, 1).Value = _
Range("IV4").End(xlToLeft).Value + 1
Range("IV13").End(xlToLeft).Resize(6, 1).Copy _
Range("IV13").End(xlToLeft).Offset(0, 1)

Next I
End Sub

 

[L. Howard Kittle]

Thanks Jim. I appreciate the help.

I tried both your examples and had mixed results. The second line of code
that copies came out red when pasted in the module. I removed the . in
front of Range(...) and seemed OK. However, that line did 10 on the first
sheet and the first line of codes that adds 1 to a value worked fine.

Should there be two .'s or 4.'s since there are only two lines of code?

The corrections you made to the code I posted acted the same way.

Weird.

Regards,
Howard

 

[L. Howard Kittle]

Hi Tom,

Right on the money! Thank you and Jim for the help, I appreciate it.

Regards,
Howard