How to loop a macro, for columns

Question

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.&nbsp;&nbsp;Below is a sample of what I am currently doing&nbsp;&nbsp;&nbsp;Columns("H:H").Select&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Columns("A:A").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :=False, Transpose:=False&nbsp;&nbsp;&nbsp; Range("G2:G3").Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Range("H2:H3").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :=False, Transpose:=False&nbsp;Columns("I:I").Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Columns("A:A").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :=False, Transpose:=False&nbsp;&nbsp;&nbsp; Range("G2:G3").Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Range("I2:I3").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :=False, Transpose:=False&nbsp;

hansvogelaar · Answer

David_Wor&nbsp;
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

david_wor · Answer

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

Forum Discussion

How to loop a macro, for columns

2 Replies

Resources