excel - 将多张工作表中的特定列复制到一张工作表中

Question

我想将所有列从“B”复制到工作表末尾到一个名为“combined”的新工作表中。工作表“组合”中的标题表与每个工作表（“A”）相同。

Sub Combine()

   ' Sheets(1).Select
   ' Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireColumn.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    Dim ws As Worksheet
    Dim wsDest As Worksheet

    Set wsDest = Sheets("Combined")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            ws.Range("B1", ws.Range("B1").End(xlToRight).End(xlDown)).Copy
            wsDest.Cells(1, Columns.Count).End(xlToLeft).Offset("B").PasteSpecial xlPasteValues
        End If
    Next ws

End Sub

标签： excelvba

Answer 1

.Offset("B")不是有效的语法

将一列向右移动你想要的.Offset(, 1)

Dim ws As Worksheet
Dim wsDest As Worksheet

Set wsDest = Sheets("Combined")

For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> wsDest.Name Then
        ws.Range("B1", ws.Range("B1").End(xlToRight).End(xlDown)).Copy
        wsDest.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
    End If
Next ws