excel - 将多张表中的数据合并到一张表中

Question

我在多个工作表（不包括合并和加载文件工作表）上的单元格 C21:F40 中有数据，我想将这些数据复制到一个主工作表（合并）上。将数据合并到合并表上后，我想对 col c 求和并删除任何重复项。

数据示例

我到处找，找不到任何可以做上述的事情，所以任何帮助将不胜感激

Option Explicit Public Sub CombineDataFromAllSheets()

Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long

'Notes: "Src" is short for "Source", "Dst" is short for "Destination"

'Set references up-front
Set wksDst = ThisWorkbook.Worksheets("Import")
lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!

'Set the initial destination range
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

'Loop through all sheets
For Each wksSrc In ThisWorkbook.Worksheets

    'Make sure we skip the "Import" destination sheet!
    If wksSrc.Name <> "Import" Then

        'Identify the last occupied row on this sheet
        lngSrcLastRow = LastOccupiedRowNum(wksSrc)

        'Store the source data then copy it to the destination range
        With wksSrc
            Set rngSrc = .Range(.Cells(2, 4), .Cells(lngSrcLastRow, lngLastCol))
            rngSrc.Copy Destination:=rngDst
        End With

        'Redefine the destination range now that new data has been added
        lngDstLastRow = LastOccupiedRowNum(wksDst)
        Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

    End If

Next wksSrc

结束子

''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''' 'INPUT : 工作表，我们将搜索工作表以找到最后一行 'OUTPUT : Long,最后占用的行'特殊情况：如果 Sheet 为空，则返回 1 Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long Dim lng As Long If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then With Sheet lng = .Cells .Find(What:="*", _ After:=.Range("B1"),_ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row End With Else lng = 1 End If LastOccupiedRowNum = lng End Function

''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''' 'INPUT : 工作表，我们将搜索工作表以找到最后一列 'OUTPUT : Long,最后占用的列 '特殊情况：如果 Sheet 为空，则返回 1 Public Function LastOccupiedColNum(Sheet As Worksheet) As Long Dim lng As Long If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then With Sheet lng = .Cells .Find(What:="*", _ After:=.Range("B1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column End With Else lng = 1 End If LastOccupiedColNum = lng End Function

标签： excelvba

Answer 1

这篇文章的某些内容对您所需要的内容含糊不清，但我认为您正在寻找的内容可以在这里找到：

将多张工作表中的数据合并到一张工作表上

但是，如果您不熟悉VBA，即使是这个网站，要完全遵循也可能会很困难。