Forum Discussion

Spacer7's avatar
Spacer7
Copper Contributor
Dec 10, 2020

Repetitive VBA code

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.    ...
Macros and VBA
adversi's avatar
adversi
Iron Contributor
Dec 10, 2020

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

  • Spacer7's avatar
    Spacer7
    Copper Contributor
    Dec 14, 2020

    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! 

    • adversi's avatar
      adversi
      Iron Contributor
      Dec 14, 2020

      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