从工作表复制范围覆盖数据

Question

我正在编写一个宏，将多个工作表(在同一工作簿内)的范围复制到工作簿中新工作表中的列。我希望从第一张工作表复制到新工作表中的范围("C2:C12021")中的值，然后从第二张工作表复制到新工作表中的列B中的范围("C2:C12021")中的值，等等。

我目前正在使用以下代码，但是宏一直复制的范围从每个表我试图结合到表的同一列，我试图将它们组合在一起。

因此，只有最后一个工作表的范围出现在合并的工作表中，我假设这是从其他工作表复制的范围只是在宏循环遍历工作表时被覆盖。

Sub CombineWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Add a worksheet named "MergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then
        'Copy target range
        Set CopyRng = sh.Range("C2:C12021")
         CopyRng.Copy
        With DestSh.Cells(Last + 1, "A")
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        End With
    End If
Next
End Sub

Answer 1

您可以将单元格的水平值引用为整数，即

.Cells(Vertical As Integer, Horizontal As Integer)

在循环开始时，设置一个计数器变量，并将其用于水平值

Dim count As Integer
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then
        count = count + 1
        'Copy target range
        Set CopyRng = sh.Range("C2:C12021")
        CopyRng.Copy
        With DestSh.Cells(last + 1, count)
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        End With
    End If
Next