Help with Copying and Inserting in VBA RRS feed

  • Question

  • I am trying to copy three columns and insert them in a particular spot elsewhere in my spreadsheet. I have VBA code that will do it for a single column:

         *Sub WorksheetAddArchive()
               'Preliminary Information
                 ColumnstoCopy = ActiveCell.Column
               'Turn off ScreenUpdating, Unprotect the Sheet
                 Application.ScreenUpdating = False
              Set SelRange = Selection
              ArchiveColumn = Range("BidsEndofColumns").Column + 1
               'Create and Format Archive Column and input Data
                  Columns(ArchiveColumn).EntireColumn.PasteSpecial xlPasteFormats
                  Columns(ArchiveColumn).PasteSpecial xlPasteValues
                  Columns(ArchiveColumn).Locked = True

    However, I need it to also select and copy the two columns next to it. My problem is which columns are copied are not set, the user may choose the set A:C or T:V or K:M. This is why we have used "ColumnstoCopy = ActiveCell.Column" up to this point. Is there an "ActiveColumn" function of some sort that would allow me to select not just the column of the single active cell, but the two to its right as well?

    Thank you!

    Thursday, November 2, 2017 11:11 PM

All replies

  • Try the following. The columns are assigned to range variables. (Note the Set at start of line to assign to objects)

    Ensure you have a backup of your workbook before running the code in case it does not do what you require.

    Feel free to get back to me if any problems.

    Sub WorksheetAddArchive()
        Dim ColumnsToCopy As Range
        Dim ArchiveColumn As Range
        Set ColumnsToCopy = Selection.EntireColumn
        Set ArchiveColumn = ColumnsToCopy.Offset(0, ColumnsToCopy.Columns.Count)
        Application.CutCopyMode = False    'In case something already copied and will insert copied data
        'Following line required to reassign range to ArchiveColumn
        'because otherwise assigned range moves to right after the insert
        Set ArchiveColumn = ColumnsToCopy.Offset(0, ColumnsToCopy.Columns.Count)
        ArchiveColumn.PasteSpecial xlPasteFormats
        ArchiveColumn.PasteSpecial xlPasteValues    'No need to copy again before PasteValues
        ArchiveColumn.Locked = True
    End Sub

    Regards, OssieMac

    • Edited by OssieMac Friday, November 3, 2017 12:58 AM
    • Proposed as answer by Terry Xu - MSFT Friday, November 3, 2017 1:10 AM
    Friday, November 3, 2017 12:56 AM
  • Note that I edited the following line of code in my previous post. (I must have pressed Enter on an intellisense dropdown instead of typing False)

    Application.CutCopyMode = False    'In case something already copied and will insert copied data

    This post is because you do not get an email notification when a post is simply edited and if you use the code directly from the original email instead of opening the post in the forum, you could have problems

    Regards, OssieMac

    Friday, November 3, 2017 1:02 AM