How to loop a macro, for columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2134074%22%20slang%3D%22en-US%22%3EHow%20to%20loop%20a%20macro%2C%20for%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134074%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20loop%20a%20macro%20crossed%20several%20columns%E2%80%A6%20can%20you%20tell%20me%20how%20to%20do%20that%20rather%20than%20copy%20and%20pasting.%20I%20am%20starting%20in%20column%20Hand%20I%20want%20to%20loop%20all%20of%20the%20columns%20to%20the%20right%20until%20don't%20have%20any%20more%20data%20in%20the%20next%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20a%20sample%20of%20what%20I%20am%20currently%20doing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumns(%22H%3AH%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Columns(%22A%3AA%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22G2%3AG3%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.CutCopyMode%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22H2%3AH3%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3BColumns(%22I%3AI%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.CutCopyMode%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Columns(%22A%3AA%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22G2%3AG3%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.CutCopyMode%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.Copy%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22I2%3AI3%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2134074%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-2134122%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20loop%20a%20macro%2C%20for%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F966420%22%20target%3D%22_blank%22%3E%40David_Wor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20n%20%3D%20Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByColumns%2C%20SearchDirection%3A%3DxlPrevious).Column%0A%20%20%20%20For%20c%20%3D%208%20To%20n%0A%20%20%20%20%20%20%20%20Columns(1).Value%20%3D%20Columns(c).Value%0A%20%20%20%20%20%20%20%20Cells(2%2C%20c).Resize(2).Value%20%3D%20Cells(2%2C%207).Resize(2).Value%0A%20%20%20%20Next%20c%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to loop a macro crossed several columns… can you tell me how to do that rather than copy and pasting. I am starting in column Hand I want to loop all of the columns to the right until don't have any more data in the next column.

 

 

Below is a sample of what I am currently doing

 

 

 

Columns("H:H").Select

    Selection.Copy

    Columns("A:A").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range("G2:G3").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("H2:H3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

 Columns("I:I").Select

    Application.CutCopyMode = False

    Selection.Copy

    Columns("A:A").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range("G2:G3").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("I2:I3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

 

2 Replies

@David_Wor 

Try this:

Sub CopyData()
    Dim c As Long
    Dim n As Long
    n = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    For c = 8 To n
        Columns(1).Value = Columns(c).Value
        Cells(2, c).Resize(2).Value = Cells(2, 7).Resize(2).Value
    Next c
End Sub

@Hans Vogelaar 

 

Hans -  Thank you.  This is so much better! I am very glad that I posted this and that you saw it.