帮助在VBA中复制和插入 [英] Help with Copying and Inserting in VBA

问题描述

我正在尝试复制三列并将其插入电子表格中其他位置的特定位置。我有一个VBA代码可以用于单个列：

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（）

           '初步信息

              ColumnstoCopy = ActiveCell.Column

          

           '关闭ScreenUpdating，取消保护工作表

              Application.ScreenUpdating = False

              ActiveSheet.Unprotect

        

         设置SelRange =选择

          ArchiveColumn = Range（" BidsEndofColumns"）。Column + 1

    

           '创建和格式化存档列并输入数据

             列（ArchiveColumn）.Insert

             列（ColumnstoCopy）.EntireColumn.Copy

             列（ArchiveColumn）.EntireColumn.PasteSpecial xlPasteFormats

             列（ColumnstoCopy）.EntireColumn.Copy

             列（ArchiveColumn）.PasteSpecial xlPasteValues

             列（ArchiveColumn）.Locked = True

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

。是否有"ActiveColumn"某种功能，我不仅可以选择单个活动单元格的列，还可以选择右边的两列？

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?

谢谢！

推荐答案

请尝试以下操作。  列分配给范围变量。 （请注意 在分配给对象的行的开头设置）

确保在运行代码之前备份工作簿，以防万一不能满足你的要求。

如果有任何问题，请随时回复我。

Feel free to get back to me if any problems.

Sub WorksheetAddArchive（）

    Dim ColumnsToCopy作为范围

    Dim ArchiveColumn作为范围

   

    ActiveSheet.Unprotect

   

   设置ColumnsToCopy = Selection.EntireColumn

   设置ArchiveColumn = ColumnsToCopy.Offset（0，ColumnsToCopy.Columns.Count）

    Application.CutCopyMode =  False    '如果已复制的内容并将插入复制的数据

    ArchiveColumn.Insert

   

    '需要以下行才能将范围重新分配给ArchiveColumn

    '因为否则分配的范围会在插入后向右移动

   设置ArchiveColumn = ColumnsToCopy.Offset（0，ColumnsToCopy.Columns.Count）

   

    ColumnsToCopy.Copy

    ArchiveColumn.PasteSpecial xlPasteFormats

    ArchiveColumn.PasteSpecial xlPasteValues    '无需在PasteValues之前再次复制

    ArchiveColumn.Locked = True

   

End Sub

Sub WorksheetAddArchive()
    Dim ColumnsToCopy As Range
    Dim ArchiveColumn As Range
   
    ActiveSheet.Unprotect
   
    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
    ArchiveColumn.Insert
   
    '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)
   
    ColumnsToCopy.Copy
    ArchiveColumn.PasteSpecial xlPasteFormats
    ArchiveColumn.PasteSpecial xlPasteValues    'No need to copy again before PasteValues
    ArchiveColumn.Locked = True
   
End Sub

这篇关于帮助在VBA中复制和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！