Range loop copy and paste vba macro problem

[badsha001]

I search everywhere but unfortunately i cant find the solution. So what i have is range "K4:M408". wht i m doing is looping but i want to change range. i will start from Range(K4:M7) and then (K8:M11) and then (K12:K15). please help me how can i do it

Sheets("Sheet1").Select
   Range("K4:M7").Select                                    
   Selection.Copy
    Sheets("sheet1").Select
    Range("A2").Select
    ActiveSheet.Paste

  i have some macro here,it will run and then now i want to repeat exactly samething again but want to change the range only.

Range("K8:M11").Select                                    
   Selection.Copy
    Sheets("sheet1").Select
    Range("A2").Select
    ActiveSheet.Paste
i ahve some macro here and then now i want to repeat exactly samething again but want to change the range only.

Range("K12:M15").Select                                    
   Selection.Copy
    Sheets("sheet1").Select
    Range("A2").Select
    ActiveSheet.Paste

 

[hippiehacker]

in which steps you want to change the range?

 

[Andrew Poulsom]

Welcome to MrExcel.

Surely you don't always want to paste to A2 on Sheet1?

 

[badsha001]

Actually i want to paste into A2 everytime, In sheet 1 i have macro which provide me the coefficient based on the value from sheet2 range (k4:m7), (k8,m11).... I record the value into next sheet3 now i want it to go back to next range.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim r As Long
    For r = 4 To 408 Step 4
        Worksheets("Sheet1").Range("K" & r).Resize(4).Copy Worksheets("Sheet1").Range("A2")
    Next r
End Sub

 

[badsha001]

This is exactly what i want but its only copying from K column i want to copy from L and M column too. My range is K4:M408 not K4:K408. Thanks in advance

Sub Test()    
Dim r As Long    For r = 4 To 408 Step 4        
Worksheets("Sheet1").Range("K" & r).Resize(4).Copy Worksheets("Sheet2").Range("A2")   
 Next r
End Sub

</PRE>

 

[badsha001]

Actullay problem solved, thanks for everyone.

What i did basically is that

Sub Test() 
Dim r As Long For r = 4 To 408 Step 4 
Worksheets("Sheet1").Range("K" & r).Resize(4).Copy Worksheets("Sheet2").Range("A2") 
Worksheets("Sheet1").Range("l" & r).Resize(4).Copy Worksheets("Sheet2").Range("b2")
Worksheets("Sheet1").Range("m" & r).Resize(4).Copy Worksheets("Sheet2").Range("c2") 
Next r
End Sub

</PRE>

 

[Andrew Poulsom]

You could have used:

Sub Test()
    Dim r As Long
    For r = 4 To 408 Step 4
        Worksheets("Sheet1").Range("K" & r).Resize(4, 3).Copy Worksheets("Sheet1").Range("A2")
    Next r
End Sub