将多个工作表合并为一个工作表

将多个工作表合并为一个工作表

我在这一点上遇到了麻烦……

我想将多个工作表合并为一个。但每次运行此代码时,它都会覆盖部分数据。它应该将所有数据复制并粘贴到一个名为“合并”的工作表中

Dim ws As Worksheet
Set ws = Worksheets.Add(Before:=Worksheets(1))
ws.Name = "Combined"

Application.DisplayAlerts = False
Dim rng As Range, rng2 As Range
Set rng = Worksheets(2).Range("A1").CurrentRegion
rng.copy ws.Range("A1")

Dim i As Integer

Dim wsCopy As Worksheet, rngCopy As Range, rngDest As Range

For i = 3 To Worksheets.Count

    Set wsCopy = Worksheets(i)
    With wsCopy
        Set rngCopy = .Range("A1").CurrentRegion
        Set rngCopy = rngCopy.Offset(1, 0).Resize(rngCopy.Rows.Count - 1)
    End With

    With ws
        Set rngDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With

    rngCopy.copy rngDest

Next i

' Delete original worksheets
For Each ws In Worksheets
    If ws.Name <> "Combined" Then ws.Delete
Next ws

我有 3 个工作表。“Dept”、“Saving”和“TimeDmnd”都有相同的标题。

谢谢。

相关内容