Excel VBA将工作表中的每个列复制到右侧的下一列

I need to accomplish something very simple: copy a complete column to the next column to the right in the same worksheet (I have around 300 of those columns in one sheet of a workbook) meaning that the macros has to copy every odd column in range to next even column so that I end up having a range full of duplicate columns. I understand that I need to use the following formula in part or in full:

我需要完成一些非常简单的事情:将完整的列复制到同一工作表中右侧的下一列(我在一张工作簿中有大约300列),这意味着宏必须复制范围内的每个奇数列到下一个偶数列,以便我最终得到一个充满重复列的范围。我知道我需要部分或全部使用以下公式:

cells(selection.row, columns.Count).end(xltoleft).offset(,1).select

What would be the complete macros though? Searched every available board and found only solutions with custom conditions. Mine should be really simple. Thank you for your input.

但是完整的宏会是什么?搜索每个可用的电路板,找到只有自定义条件的解决方案。我应该很简单。谢谢您的意见。

3 个解决方案

#1

Try (might need some error handling). Rather than copying entire columns I am using column A to determine the last row of data in the sheet (you can change this) then I am looping the even columns setting them equal to the prior odd columns.

尝试(可能需要一些错误处理)。我没有复制整个列,而是使用A列来确定工作表中的最后一行数据(您可以更改它)然后我循环偶数列,将它们设置为等于先前的奇数列。

Option Explicit

Sub test()

    Dim loopRange As Range

    Set loopRange = ThisWorkbook.ActiveSheet.Columns("A:AE")

    Dim lastRow As Long

    With ThisWorkbook.ActiveSheet

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    End With

    Dim currentColumn As Long

    With loopRange

        For currentColumn = 2 To .Columns.Count Step 2

            .Range(.Cells(1, currentColumn), .Cells(lastRow, currentColumn)) = .Range(.Cells(1, currentColumn - 1), .Cells(lastRow, currentColumn - 1)).Value

        Next currentColumn

    End With

End Sub

If you know the last row:

如果你知道最后一行:

 Option Explicit

    Sub test()

        Dim loopRange As Range

        Set loopRange = ThisWorkbook.ActiveSheet.Columns("A:AE")

        Const lastRow As Long = 108

        Dim currentColumn As Long

        With loopRange

            For currentColumn = 2 To .Columns.Count Step 2

                .Range(.Cells(1, currentColumn), .Cells(lastRow, currentColumn)) = .Range(.Cells(1, currentColumn - 1), .Cells(lastRow, currentColumn - 1)).Value

            Next currentColumn

        End With

    End Sub

#2

I'm not entirely sure I understood the issue, but please find below a suggestion. The code may be a bit messy since I used a recorded macro:

我不完全确定我理解这个问题,但请在下面找到一个建议。因为我使用了录制的宏,所以代码可能有点乱:

Sub CopyRows()

Range("A1").Activate

While Not IsEmpty(ActiveCell)
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(0, 1).Range("A1").Select
Wend

End Sub

#3

If you're hoping to essentially duplicate every column by inserting a copy of each column to the right I think you need the below code.

如果您希望通过向右侧插入每列的副本来实质上复制每一列,我认为您需要以下代码。

i.e. this copies columns:

即复制列:

A | B | C 
---------
A | B | C 
1 | 2 | 3 

to

A | B | C | D | E | F
---------------------
A | A | B | B | C | C
1 | 1 | 2 | 2 | 3 | 3

VBA

Option Explicit

Sub CopyAllColsOneToRight()

    Dim ws As Worksheet
    Dim lastCol As Long
    Dim lastRow As Long
    Dim currentCopyCol As Long

    Application.ScreenUpdating = False 'optimise performance by not updating the screen as we move stuff
    Set ws = ActiveSheet
    lastCol = GetLastUsedColumn(ws).Column
    lastRow = GetLastUsedRow(ws).Row

    For currentCopyCol = lastCol To 1 Step -1
        CopyColumnInsertRight ws, lastRow, currentCopyCol
        'CopyColumn ws, lastRow, currentCopyCol, lastRow, currentCopyCol * 2
        'CopyColumn ws, lastRow, currentCopyCol, lastRow, currentCopyCol * 2 - 1
    Next

End Sub

Sub CopyColumnInsertRight(ByRef ws As Worksheet, fromLastRow, fromCol)
    Dim fromRange As Range
    Set fromRange = ws.Range(ws.Cells(1, fromCol), ws.Cells(fromLastRow, fromCol))
    fromRange.Copy
    fromRange.Insert Shift:=XlDirection.xlToRight
End Sub

'Sub CopyColumn(ByRef ws As Worksheet, fromLastRow, fromCol, toLastRow, toCol)
'   Dim fromRange As Range
'   Dim toRange As Range
'   Set fromRange = ws.Range(ws.Cells(1, fromCol), ws.Cells(fromLastRow, fromCol))
'   Set toRange = ws.Range(ws.Cells(1, toCol), ws.Cells(toLastRow, toCol))
'   toRange.Value2 = fromRange.Value2
'End Sub

Function GetLastUsedColumn(ByRef ws As Worksheet) As Range
    Set GetLastUsedColumn = ws.Cells.Find( _
        What:="*" _
        , After:=ws.Cells(1, 1) _
        , LookIn:=XlFindLookIn.xlFormulas _
        , LookAt:=XlLookAt.xlPart _
        , SearchOrder:=XlSearchOrder.xlByColumns _
        , SearchDirection:=XlSearchDirection.xlPrevious _
        , MatchCase:=False _
    )
End Function

Function GetLastUsedRow(ByRef ws As Worksheet) As Range
    Set GetLastUsedRow = ws.Cells.Find( _
        What:="*" _
        , After:=ws.Cells(1, 1) _
        , LookIn:=XlFindLookIn.xlFormulas _
        , LookAt:=XlLookAt.xlPart _
        , SearchOrder:=XlSearchOrder.xlByRows _
        , SearchDirection:=XlSearchDirection.xlPrevious _
        , MatchCase:=False _
    )
End Function

Notes on the code:

代码说明:

• We disable screen updating; this avoids refreshing the UI whilst the macro runs, making the process more efficient.

我们禁用屏幕更新;这可以避免在宏运行时刷新UI,从而提高流程效率。

• We get the last populated column so that instead of copying every column on the worksheet we can limit those copied to the ones which make a difference (i.e. much faster for spreadsheets using less that the full number of columns; which will be true of most)

我们得到最后填充的列,以便不是复制工作表上的每一列,而是将那些复制到那些产生差异的列(即使用少于完整列数的电子表格快得多;大多数都是如此)

• We get the last populated row so that instead of copying entire columns we only copy populated rows. We could check for the last used cell per row, but that's likely less efficient since typically the last row will be the same for most columns in range. Also, when using the insert method this is required to ensure that xlToRight doesn't cause cells to be shifted into the wrong columns.

我们得到最后一个填充的行,这样我们只复制填充的行,而不是复制整个列。我们可以检查每行最后使用的单元格,但这可能效率较低,因为通常最后一行对于范围内的大多数列都是相同的。此外,在使用insert方法时,需要确保xlToRight不会导致单元格移入错误的列。

• Our for loop has Step -1 since if we went from left to right we'd overwrite columns to the right as we copied others (e.g. copying A to B overwrites what's in B, then when we copy B to C we're actually copying the copy). Instead we work backwards so that we're always copying to blank columns or to columns we've previously copied.

我们的for循环有步骤-1,因为如果我们从左到右,我们会在我们复制其他列时向右覆盖列(例如,复制A到B会覆盖B中的内容,然后当我们将B复制到C时,我们实际上是在复制副本)。相反,我们向后工作,以便我们始终复制到空白列或我们之前复制的列。

• I've provided a commented out version which only copies values (faster than copying formats), and another version which uses Insert to create the new columns. One may perform better than the other, but I've not tested so far (NB: The copy has to copy twice as many cells as it doesn't keep the originals but creates 2 copies, whilst the insert method keeps the originals and inserts a copy to the right, but has the additional overhead of copying formatting data).

我提供了一个注释掉的版本,它只复制值(比复制格式更快),另一个版本使用Insert来创建新列。一个可能比另一个表现更好,但到目前为止我还没有测试过(注意:副本必须复制两倍的单元格,因为它不保留原件但创建了2个副本,而insert方法保留了原件和插入右侧的副本,但具有复制格式化数据的额外开销。