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

%3CLINGO-SUB%20id%3D%22lingo-sub-199781%22%20slang%3D%22en-US%22%3EGetting%20Macro%20to%20loop%20and%20get%20the%20range%20to%20change%20each%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199781%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20hope%20you%20can%20help!%20I%20recorded%20a%20macro%20and%20I%20want%20to%20edit%20it%20to%20make%20it%20work%20for%20my%20purposes.%20I%20want%20the%20macro%20to%20copy%20a%20row%20of%20cells%20into%20a%20specific%20location.%20Then%20have%20the%20macro%20copy%20my%20results%20and%20paste%20them%20into%20the%20row%20where%20I%20did%20the%20original%20copy%20from.%20So%20I%20want%20to%20have%20two%20variable%20ranges%20and%202%20set%20ones.%20Below%20is%20a%20copy%20of%20what%20is%20happening.%20I%20want%20to%20loop%20it%20X%20number%20of%20times%20and%20have%20the%20ranges%20C129%3AL129%20and%20N129%20increase%20by%20one%20each%20time%20for%20the%20x%20number%20of%20times.%20So%20after%20the%20analysis%20it%20goes%20to%20C130%3AL130%2C%20copies%2C%20and%20pastes%20back%20into%20B121.%20Any%20help%20is%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Macro23()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Macro23%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Keyboard%20Shortcut%3A%20Ctrl%2Bl%3CBR%20%2F%3E'%3CBR%20%2F%3EFor%20X%20%3D%20129%20To%20130%3CBR%20%2F%3ERange(%22C129%3AL129%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22B121%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERange(%22M120%3AR120%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22N129%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ENext%20X%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-199781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-199990%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Macro%20to%20loop%20and%20get%20the%20range%20to%20change%20each%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199990%22%20slang%3D%22en-US%22%3E%3CP%3EOoooohh!%20That's%20now%20you%20combine%20the%20location%20and%20the%20variable.%20I%20was%20trying%20like%20%22C%22%20%2B%20x%20blah%20blah.%20But%20you%20have%20to%20use%20the%20%26amp%3B.%20Thanks%20so%20much!%20Works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-199838%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Macro%20to%20loop%20and%20get%20the%20range%20to%20change%20each%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199838%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F152149%22%20target%3D%22_blank%22%3E%40Tyler%20Dyer%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFor%20X%20%3D%20129%20To%20130%3CBR%20%2F%3ERange(%22C%22%20%26amp%3B%20X%20%26amp%3B%20%22%3AL%22%20%26amp%3B%20X).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22B121%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERange(%22M120%3AR120%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22N%22%20%26amp%3B%20X).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3E%3CSPAN%3EApplication.CutCopyMode%20%3D%20False%3C%2FSPAN%3E%3CBR%20%2F%3ENext%20X%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1950254%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Macro%20to%20loop%20and%20get%20the%20range%20to%20change%20each%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1950254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53299%22%20target%3D%22_blank%22%3E%40Logaraj%20Sekar%3C%2FA%3E%26nbsp%3BHello%20could%20you%20help%20me%20as%20well%2C%20I%20need%20to%20make%20a%20loop%20to%20and%20add%20number%20in%20range%20A.%20So%20the%20sequence%20should%20be%20as%20follows%2C%20number%2024%20for%20100%20times%20in%20a%20column%20after%20enter%200%2C%20again%2024%20for%20100%20times%2C%20next%200%20two%20times%20and%20again%2024%20for%20100%20times%2C%20after%200%20for%203%20times.%20So%20having%20constantly%2024%20for%20100%20times%20and%200%20increased%20by%201%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951134%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20Macro%20to%20loop%20and%20get%20the%20range%20to%20change%20each%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888925%22%20target%3D%22_blank%22%3E%40Timur_Haliulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20explain%20more%20with%20example%2C%20so%20that%20i%20can%20write%20macro.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.