select one range and then go to next
 

Hi all,

This is my first post. I am new to excel macros. Please help me.

I need to copy from range(H2:M2) in sheet1 and paste it into range(A2)
in sheet2, then go to range(H5:M5), copy that and paste in range(A3)
and so on..

How do i increment the range in a For loop?

Thank you,

ET.


--
excel_lent
------------------------------------------------------------------------
excel_lent's Profile: http://www.excelforum.com/member.php...o&userid=31738
View this thread: http://www.excelforum.com/showthread...hreadid=514524

select one range and then go to next
 

Sub abc()
Dim j as Long, i as Long
j = 2
for i = 2 to 100 step 3
cells(i,"H").Resize(1,6).copy _
worksheets("Sheet2").Cells(j,1)
j = j + 1
Next
end sub

--
Regards,
Tom Ogilvy


"excel_lent" wrote
in message ...

Hi all,

This is my first post. I am new to excel macros. Please help me.

I need to copy from range(H2:M2) in sheet1 and paste it into range(A2)
in sheet2, then go to range(H5:M5), copy that and paste in range(A3)
and so on..

How do i increment the range in a For loop?

Thank you,

ET.


--
excel_lent
------------------------------------------------------------------------
excel_lent's Profile:
http://www.excelforum.com/member.php...o&userid=31738
View this thread: http://www.excelforum.com/showthread...hreadid=514524

select one range and then go to next
 

Hi,

One way:

Sub CopyHM()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim iLastrow As Long, r As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set orng = ws2.Range("a2")

ws1.Select
With ws1
iLastrow = .Cells(Rows.count, "H").End(xlUp).Row '<=== last row in column H
For r = 2 To iLastrow Step 3 '<=== increment loop by 3 until iLastrow reached
..Cells(r, "H").Resize(1, 6).Copy orng '<=== Resize selects colums H to M (6
columns)
Set orng = orng.Offset(1, 0) '<=== set range to next row in Sheet2
Next r
End With
End Sub


HTH

"excel_lent" wrote:


Hi all,

This is my first post. I am new to excel macros. Please help me.

I need to copy from range(H2:M2) in sheet1 and paste it into range(A2)
in sheet2, then go to range(H5:M5), copy that and paste in range(A3)
and so on..

How do i increment the range in a For loop?

Thank you,

ET.


--
excel_lent
------------------------------------------------------------------------
excel_lent's Profile: http://www.excelforum.com/member.php...o&userid=31738
View this thread: http://www.excelforum.com/showthread...hreadid=514524

select one range and then go to next
 

Hi ET,

Try this. Copy and right click the sheet 1 tab and click on View Code.
Paste the code in the sheet module, (the white space). Alt+Tab back to the
worksheet and from the Forms tool bar produce a button and assign the macro
to it.

Change For i = 1 To 4 to the number of times you want to loop.

Sub CopyIt()
Dim i As Integer
Range("H2").Select
For i = 1 To 4
ActiveCell.Resize(1, 6).Copy _
Sheets("Sheet2").Range("A100").End(xlUp).Offset(1, 0)
ActiveCell.Offset(4, 0).Select
Next
End Sub

HTH
Regards,
Howard

"excel_lent" wrote
in message ...

Hi all,

This is my first post. I am new to excel macros. Please help me.

I need to copy from range(H2:M2) in sheet1 and paste it into range(A2)
in sheet2, then go to range(H5:M5), copy that and paste in range(A3)
and so on..

How do i increment the range in a For loop?

Thank you,

ET.


--
excel_lent
------------------------------------------------------------------------
excel_lent's Profile:
http://www.excelforum.com/member.php...o&userid=31738
View this thread: http://www.excelforum.com/showthread...hreadid=514524