Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet loop won't loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet loop won't loop
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 -- HTH... Jim Thomlinson "L. Howard Kittle" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet loop won't loop
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 -- Regards, Tom Ogilvy "L. Howard Kittle" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet loop won't loop
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet loop won't loop
Hi Tom,
Right on the money! Thank you and Jim for the help, I appreciate it. Regards, Howard "L. Howard Kittle" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
how do you loop through each worksheet? | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |