Getting Macro to loop and get the range to change each time

Copper Contributor

Hi! I hope you can help! I recorded a macro and I want to edit it to make it work for my purposes. I want the macro to copy a row of cells into a specific location. Then have the macro copy my results and paste them into the row where I did the original copy from. So I want to have two variable ranges and 2 set ones. Below is a copy of what is happening. I want to loop it X number of times and have the ranges C129:L129 and N129 increase by one each time for the x number of times. So after the analysis it goes to C130:L130, copies, and pastes back into B121. Any help is much appreciated!

 

Sub Macro23()
'
' Macro23 Macro
'
' Keyboard Shortcut: Ctrl+l
'
For X = 129 To 130
Range("C129:L129").Select
Selection.Copy
Range("B121").Select
ActiveSheet.Paste
Range("M120:R120").Select
Application.CutCopyMode = False
Selection.Copy
Range("N129").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next X

End Sub

4 Replies

Hi @Tyler Dyer

 

For X = 129 To 130
Range("C" & X & ":L" & X).Select
Selection.Copy
Range("B121").Select
ActiveSheet.Paste
Range("M120:R120").Select
Application.CutCopyMode = False
Selection.Copy
Range("N" & X).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next X

Ooooohh! That's now you combine the location and the variable. I was trying like "C" + x blah blah. But you have to use the &. Thanks so much! Works perfectly!

@Logaraj Sekar Hello could you help me as well, I need to make a loop to and add number in range A. So the sequence should be as follows, number 24 for 100 times in a column after enter 0, again 24 for 100 times, next 0 two times and again 24 for 100 times, after 0 for 3 times. So having constantly 24 for 100 times and 0 increased by 1 cell

 

Thank you in advance

@Timur_Haliulin 

 

Can you explain more with example, so that i can write macro.