darylb
01-21-2009, 08:19 AM
I'm writing a macro to select and copy certain blocks of data arranged vertically in one sheet and paste horiztonally in another sheet. I made a FOR loop do this cell by cell but it is very slow so I want to use ranges however I cannot figure out how to refer to a range using index numbers so that I can increment those variables.
Currently I have:
For k = 1 To 26
For c = 2 To 10
For y = 1 To 90
Sheets(n).Select
Cells(r, c).Select
Selection.Copy
Sheets(o).Select
Cells(i, j).Select
ActiveSheet.Paste
i = i + 1
r = r + 1
Next y
r = r - 90
i = 1
j = j + 1
Next c
r = r + 285
Next k
I could replace the two inner loops with range references if these is a way I can refer to the column number of where i'm pasting as a variable which I can increment.
Currently I have:
For k = 1 To 26
For c = 2 To 10
For y = 1 To 90
Sheets(n).Select
Cells(r, c).Select
Selection.Copy
Sheets(o).Select
Cells(i, j).Select
ActiveSheet.Paste
i = i + 1
r = r + 1
Next y
r = r - 90
i = 1
j = j + 1
Next c
r = r + 285
Next k
I could replace the two inner loops with range references if these is a way I can refer to the column number of where i'm pasting as a variable which I can increment.