How to insert when pasting column?

user_12747764 0
Viewed 80

In a worksheet I would like to copy and paste columns with a button.

It should copy two columns and insert them in the first free column that it can find.

For example it would be F and G. If I press the button again it should do same again and insert in H and I this time.

My VBA code for an Active-X Button:

Private Sub CommandButton1_Click()
Columns("D:E").Copy
CommandButton1.TopLeftCell.EntireColumn.Insert
Application.CutCopyMode = False
End Sub

What do I change to paste in the first free column.

TopLeftCell is false.

activex vba excel
1 Answers

Copy After Last Column

  • It is assumed that the worksheet is not filtered.
Sub CopyToRight()
    
    Dim lcCell As Range
    Set lcCell = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
    If lcCell Is Nothing Then Exit Sub
    
    Columns("D:E").Copy lcCell.EntireColumn.Offset(, 1)

End Sub
  • In the following two examples, it is additionally assumed that there are no hidden cells (rows or columns) and that the first row of each worksheet column contains data.

Copy After Last Column

Sub CopyAfterAll()
    With Columns("D:E")
        .Copy .Worksheet.Cells(1, .Worksheet.Columns.Count) _
            .End(xlToLeft).Offset(, 1)
    End With
End Sub

Insert After Last Column

Sub InsertAfterAll()
    With Columns("D:E")
        .Copy
        .Worksheet.Cells(1, .Worksheet.Columns.Count).End(xlToLeft) _
            .Offset(, 1).EntireColumn.Insert
    End With
    Application.CutCopyMode = False
End Sub
  • The result of the following two examples is the same (due to entire columns), yet there is a difference: if you uncomment the Debug.Print lines, you will notice that in the After procedure the range stays D:E, while in the Before procedure (probably your requirement) the range becomes F:G.

Insert Right After

Sub InsertRightAfter()
    With Columns("D:E")
        .Copy
        .Offset(, .Columns.Count).Insert
        'Debug.Print .Address(0, 0) 
    End With
    Application.CutCopyMode = False
End Sub

Insert Right Before

Sub InsertRightBefore()
    With Columns("D:E")
        .Copy
        .Insert
        'Debug.Print .Address(0, 0) 
    End With
    Application.CutCopyMode = False
End Sub
user_98140690
Related