View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
Don Guillett Don Guillett is offline
external usenet poster
 
Posts: 10,124
Default Help with nested for loops

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


--
Don Guillett
SalesAid Software

wrote in message
ps.com...
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 A19:D30:



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