Loop to specific rows

[Omar]

I need to write a macro that copies cells in a specific row and then
loops to the next row, next row being 5 rows below.

So, copy cells C7, D7 to H7
then, Copy the next set C12, D12 ... H12
and so on.

I would assume this would be done using 2 loops, one for goings
through the columns and one for the rows, but I could be wrong.

I am not sure of the syntax, so if anyone can give me some tips, that
would be great.
Thanks.

 

[HI]

Don't know if this is what you wanted. Following macro will add number
1 on cells C7, D7 to H7 and C12, D12 ... H12 etc.
You replace the 1 with your formula

Sub TEST()
Range("C7").Select
For i = 0 To 10
For j = 0 To 5
ActiveCell.Offset(i * 5, j).Value = 1
Next j
Next i
End Sub

 

[Don Guillett]

for i = 7 to whatever step 5
cells(i,"c").resize(,2) copy cells(i,"h")
next i

 

[Omar]

Don't know if this is what you wanted. Following macro will add number
1 on cells C7, D7 to H7 and C12, D12 ... H12 etc.
You replace the 1 with your formula

Sub TEST()
Range("C7").Select
For i = 0 To 10
For j = 0 To 5
ActiveCell.Offset(i * 5, j).Value = 1
Next j
Next i
End Sub

Thanks, that makes sense.

I had another question
my original formula was just extracting the first 3 chars from a
specific cell, now as I have to loop through the columns and rows, the
following won't work:
id = Range("C7").Characters(1, 3).Text

Using you method, how would I integrate this into the code?

ActiveCell.Offset(i * 5, j).Value = 1 is inserting '1' into those
cells. How do I make it copy those cells instead?

 

[Omar]

I am tying the following, but it doesn't seem to work

Range("B7").Select
For i = 0 To 10
For j = 0 To 6
ActiveCell.Offset(i * 5, j).Select
Selection.Copy

Windows("Test.xls").Activate
ActiveCell.Range("A2").Select
ActiveSheet.Paste

Next j
Next i

 

[Don Guillett]

Run from the sheet with the source data and change sheet27 to suit
sub copyevery7()
With Sheets("sheet27")
..Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub

 

[Don Guillett]

Didn't notice the part about another file. Try this from the source workbook
& sheet if BOTH open.

Sub copyevery7()
With Workbooks("test.xls").Sheets("sheet1")
..Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub

 

[Omar]

When I use this, the values that are being copied are for some reason
pasted in cell H2, I2...etc
What does the Resize function do?

Any ideas?

 

[Don Guillett]

From this data in the source workbook
aaa bbb ccc ddd
a 1 2 3
b 2 3 4
c 3 4 5
d 4 5 6
e 5 6 7
f7 6 7 8
g 7 8 9
h 8 9 10
i 9 10 11
j 10 11 12
k 11 12 13
l 12 13 14
m7 13 14 15
n 14 15 16
o 15 16 17
p 16 17 18
q 17 18 19
r 18 19 20
s 19 20 21
t7 20 21 22
u 21 22 23
v 22 23 24
w 23 24 25
x 24 25 26
y 25 26 27

You should have in Test.xls
f7 6 7 8
m7 13 14 15
t7 20 21 22

Is that what you wanted?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

When I use this, the values that are being copied are for some reason
pasted in cell H2, I2...etc
What does the Resize function do?

Any ideas?

Didn't notice the part about another file. Try this from the source
workbook
& sheet if BOTH open.

Sub copyevery7()
With Workbooks("test.xls").Sheets("sheet1")
.Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
in message

Run from the sheet with the source data and change sheet27 to suit
sub copyevery7()
With Sheets("sheet27")
.Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub

 

[HI]

Sorry Omar did not mean to leave you hanging. I don't really
understand what you meant, but for your question for me how to make my
code copy... Now I must warn you this is not the best way... well
almost anything but here goes. This macro will copy values from cells
C7 etc to Test.xls to column A so that first value (C7) will go to A2
and D7 in A3 and so on. I asume you will have both original data.xls
and test.xls open and nothing else.

Sub test()
Range("c7").Select
For i = 0 To 10
For j = 0 To 6
ActiveCell.Offset(i * 5, j).Copy

Windows("Test.xls").Activate
Range("A" & 2 + k).Select
Selection.PasteSpecial Paste:=xlValues
ActiveWindow.ActivateNext
k = k + 1
Next j
Next i
End Sub


hope this helps

 

[Omar]

Don, yes that is what I wanted, I got it working now somewhat.

"HI", Sorry for not being clear, what I have is this.
Original.xls:

Row 1 User Monday Tuesday
Row 7 jack 123/45 876/54
Row12 john 526/4 456/3
Row17 Bob 231/e 314/tw

what I have to do is extract the user and id field and put it in
another xls.
I figured out how to do this without the loop. with the loop i was
having some trouble.

Another complication is that, from the id field, i just need to pull
the first three chars.so at the end the new file will look something
like:

Row 1 User ID
Row 2 jack 123
Row 3 john 526

I'll try playing around with your code and see if I can come up with
anything.

 

[Don Guillett]

Why do you keep changing your desires with each post. You really need to
learn to ask for WHAT YOU WANT the first time. You will need to use
something like

Cells(i, "c").Copy .Cells(lr, "h")
left(Cells(i, "d"),3).Copy .Cells(lr, "i")