How to loop a macro, for columns

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.