CODEWITHSUNDEEP
excelvbainsertmultiple-columns
NatAc
NatAc

Reputation: 11

Insert multiple columns to the right in sequential order

Aim: Selecting multiple columns, copying them and then inserting all copied columns each to its right. Example: Selection of columns A, B and C, and duplicating them in sequential order so that I end up with SIX columns where: A=B, C=D and E=F.

Any idea on how I can modify "Shift:=xlToRight" so that columns are inserted to each one's right, rather than at the right end of the entire range?

Here is the code I tried.

    ActiveCell.EntireColumn.Select

    Selection.Copy

    Selection.Insert Shift:=xlToRight

Actual Result: Multiple columns are select and copied correctly but inserted at the end of the range. That is, Columns A, B and C are correctly selected and copied, and then inserted as D E and F, where: A=D, B=E and C=F.

Upvotes: 1

Views: 111

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Duplicate Columns

  • Why these complications?
  • Flexibility. You don't have to select entire columns or adjacent columns. The selected cells can be in any row or column and they can be selected in any order. The line Set rg = Selection.EntireColumn takes care of all issues that might occur without it.
Option Explicit

Sub DuplicateColumns()
    
    If Selection Is Nothing Then Exit Sub ' no selection
    If Not TypeOf Selection Is Range Then Exit Sub ' not a range
    
    Dim rg As Range: Set rg = Selection.EntireColumn
    
    Dim arg As Range, crg As Range, a As Long, c As Long 
    
    For a = rg.Areas.Count To 1 Step -1
        Set arg = rg.Areas(a)
        For c = arg.Columns.Count To 1 Step -1
            Set crg = arg.Columns(c)
            crg.Copy
            crg.Offset(, 1).Insert xlShiftToRight, xlFormatFromLeftOrAbove
        Next c
    Next a
    
End Sub

Upvotes: 1

Related Questions