Feb 14 2021 11:32 AM
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
Feb 14 2021 12:03 PM
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
Feb 14 2021 08:50 PM
Hans - Thank you. This is so much better! I am very glad that I posted this and that you saw it.