Repetitive VBA code

%3CLINGO-SUB%20id%3D%22lingo-sub-1977297%22%20slang%3D%22en-US%22%3ERepetitive%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977297%22%20slang%3D%22en-US%22%3E%3CP%3EExcuse%20my%20VBA%20ignorance%2C%20I%20started%20recording%20a%20macro%20which%20is%20very%20repetitive.%26nbsp%3B%20I%20need%20to%20repeat%20this%20code%20(pasted%20below)%20changing%20the%20column%20each%20time%20starting%20with%20column%20AG%20all%20the%20way%20to%20DY.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20table%20of%20results%20which%20spits%20out%20a%20different%20set%20of%20results%20for%20a%20change%20in%20the%20main%20variable%20(Row%2027).%26nbsp%3B%20The%20code%20pastes%20increments%20of%20the%20main%20variable%20into%20a%20key%20cell%20in%20my%20model%20and%20then%20cuts%20and%20pasts%20the%20results%20from%20row%2028.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20did%20this%20via%20record%20macro%20and%20it%20took%20an%20hour%20and%20broke%20my%20worksheet...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20guessing%20there%20must%20be%20a%20neater%20way%20rather%20than%20pasting%20this%20code%20over%20and%20over%20again%20changing%20the%20column%20letter%20each%20time...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20points%20would%20be%20humbly%20and%20gratefully%20received!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%22Ag27%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22AE12%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERange(%22AD12%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22Ag28%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1977297%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1977520%22%20slang%3D%22en-US%22%3ERe%3A%20Repetitive%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F897886%22%20target%3D%22_blank%22%3E%40Spacer7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20share%20a%20screenshot%20or%20sample%20workbook%20with%20no%20confidential%20information%3F%20At%20the%20moment%2C%20the%20VBA%20doesn't%20provide%20enough%20detail%20on%20the%20copy%20and%20paste%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1985614%22%20slang%3D%22en-US%22%3ERe%3A%20Repetitive%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985614%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%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20reply.%26nbsp%3B%20I%20can't%20paste%20in%20any%20more%20detail%20as%20its%20sensitive.%20But%20essentially%20the%20only%20think%20that%20is%20changing%20is%20the%20letters%20'%3CSPAN%3EAG'%2C%20i.e.%20the%20column%20label%2C%20and%20they%20are%20increasing%20by%201%2C%20about%2080%20times%2C%20repeating%20the%20rest%20of%20the%20code%20over%20and%20over%20again.%26nbsp%3B%20The%20Macro%20works%2C%20but%20take%20a%20couple%20of%20minutes%20to%20process%2C%20and%20I'm%20sure%20there%20must%20be%20cleaner%20way%20of%20coding%20than%20repeating%20the%20whole%20section%20of%20code%20over%20and%20over%20again.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAppreciate%20if%20that's%20still%20not%20enough%20information!%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Excuse my VBA ignorance, I started recording a macro which is very repetitive.  I need to repeat this code (pasted below) changing the column each time starting with column AG all the way to DY.  

 

I have created a table of results which spits out a different set of results for a change in the main variable (Row 27).  The code pastes increments of the main variable into a key cell in my model and then cuts and pasts the results from row 28. 

 

I already did this via record macro and it took an hour and broke my worksheet... 

 

I'm guessing there must be a neater way rather than pasting this code over and over again changing the column letter each time... 

 

Any points would be humbly and gratefully received!  

 

Range("Ag27").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE12").Select
ActiveSheet.Paste
Range("AD12").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("Ag28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

3 Replies

@Spacer7 

Can you share a screenshot or sample workbook with no confidential information? At the moment, the VBA doesn't provide enough detail on the copy and paste

Hi @adversi thanks for the reply.  I can't paste in any more detail as its sensitive. But essentially the only think that is changing is the letters 'AG', i.e. the column label, and they are increasing by 1, about 80 times, repeating the rest of the code over and over again.  The Macro works, but take a couple of minutes to process, and I'm sure there must be cleaner way of coding than repeating the whole section of code over and over again.  

 

Appreciate if that's still not enough information! 

@Spacer7 

To improve the macro, you can adjust the Range portion of the selection to expand the number of cells being copied.

 

In this case, if you want to copy cells from Row 27 to Row 28, starting with column AG to (hypothetical) AZ, the copy and paste macro would be:

Range("AG27:AZ27").Copy
Range("AG28:AZ28").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

There are more ways to optimize this code, but this elimination of individual cells being copied one at time should improve the performance