Help with nested for loops

[yousaf.hassan]

Hi

I am trying to write a macro to accomplish a task.

Here is the scenario:

From sheet1 to sheet20, there is a table in range A1930:

Apr-05 * * *
May-05 11 * 7
Jun-05 * 6 *
Jul-05 * * *
Aug-05 * * *
Sep-05 * * *
Oct-05 6 * 8
Nov-05 * 9 *
Dec-05 * * *
Jan-06 9 * *
Feb-06 * * *
Mar-06 * * *

In sheet21 I want to create 20 tables. All the Apr-05 rows will
create
the first table, May-05 rows will create the second table, so on and
so forth.


The code I have come up with is:


Sub myTables()
Dim RowNdx1 As Integer
Dim RowNdx2 As Integer
Dim RowNdx3 As Integer


For RowNdx1 = 1 To 20 Step 1
For RowNdx2 = 17 To 30 Step 1
For RowNdx3 = 1 To 240 Step 1
Sheets("sheet" & RowNdx1).Select
Rows(RowNdx2 & ":" & RowNdx2).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("sheet21").Select
Rows(RowNdx3 & ":" & RowNdx3).Select
ActiveSheet.Paste
Next RowNdx3
Next RowNdx2
Next RowNdx1


End Sub


This code runs and goes into an infinite loop. I am sure I have not
coded it correctly, but don't know where the mistake is.


Any help will be very appreciated.


Thanks

 

[JLatham]

I will look at this in more detail - but your most immediate problem is that
you do not need/want the RowNdx3 loop. Each time RowNdx2 increases, you are
copying that row 240 times into sheet21. This is possibly why you think it
is going into an infinite loop. It is not, it is just taking 240 times as
long to complete as you thought it would, and you are seeing 240 copies of
each row from each of the other 20 sheets, which is not what you want.

A quick rewrite of it (not completely fixed) would be:

RowNdx3 = 1 ' Initialize pointer
For RowNdx1 = 1 To 20 Step 1
For RowNdx2 = 17 To 30 Step 1
Sheets("sheet" & RowNdx1).Select
Rows(RowNdx2 & ":" & RowNdx2).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("sheet21").Select
Rows(RowNdx3 & ":" & RowNdx3).Select
ActiveSheet.Paste
RowNdx3 = RowNdx3 + 1
Next RowNdx2
Next RowNdx1

We still need to fix RowNdx2 so that it groups the entries properly on
Sheet21.
I think (again, in a rush here) that we actually should reverse the
remaining loops.

Also, we can speed things up by using Range variables to do the copy and
paste MUCH faster than physically .Select-ing the various pages.

 

[JLatham]

I believe this will fix it all for you. Note that where you see = _ that
there is a space between the = symbol and the _ character.

Sub MakeTables()

Dim sheetNdx As Integer
Dim RowNdx1 As Integer
Dim RowNdx2 As Integer
Dim RngSource As Range
Dim RngDestination As Range

For RowNdx1 = 17 To 30
For sheetNdx = 1 To 20
RowNdx2 = RowNdx2 + 1
Set RngSource = _
Worksheets("Sheet" & sheetNdx).Rows(RowNdx1 & ":" & RowNdx1)
Set RngDestination = _
Worksheets("Sheet21").Rows(RowNdx2 & ":" & RowNdx2)
RngDestination.Value = RngSource.Value
Next
'if you want an empty row between
'the groups then
RowNdx2 = RowNdx2 + 1
Next

End Sub

 

[Don Guillett]

try this idea

Sub maketablesDon()
For i = 1 To 20
c = 1
For ii = 17 To 30
Sheets("sheet" & i).Cells(ii, 1).Resize(, 4).Copy _
Sheets("sheet21").Cells(i+1, c)
c = c + 4
Next ii
Next i
End Sub

 

[Zuzeppeddu]

Thanks ever so much to both of you for solving this problem. Both
macros are doing their job perfectly and I am getting the results I
wanted.

Regards
Yousaf

 

[JLatham]

Don's is probably the more efficient of the two. But at least with mine you
also get to see the difference between it and your original and learn a
little more about how loops within loops work.

(I don't use .Resize much at all - another operation I must use/learn more
about myself - seems so handy at times like this).