在VBA中创建具有多个源的Excel合并工作表 [英] Create Excel Consolidated Worksheet with multiple sources in VBA

问题描述

我正在尝试使用VBA将多个工作表合并到一个工作表上.如何告诉VBA仅合并可见的工作表?

I am trying to Consolidate multiple Worksheets on one Worksheet using VBA. How do I tell VBA to only consolidate the worksheets that are visible?

推荐答案

我从

I took Jerry Sullivan's answer from http://www.mrexcel.com/forum/excel-questions/620641-using-visual-basic-applications-perform-consolidate-function.html and tweaked it. The MSDN site was of some help in understanding the arguments, for example that the array of ranges must contain fully qualified addresses in R1C1 style.

当然，您没有提供有关如何尝试使用 Consolidate 的详细信息，因此此答案是通用的.它使用Sum函数合并活动工作簿中所有可见工作表中的已用范围:

Of course you gave no details on how you are trying to use Consolidate so this answer is generic. It consolidates the used range from all visible sheets in the active workbook using the Sum function:

Sub Consolidate_Totals()
    Dim ws As Worksheet
    Dim sArray As Variant, i As Integer
    ReDim sArray(1 To 1)

    '---Make Array with Named Ranges to be Consolidated
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible And ws.Name <> "Sheet1" Then
            i = i + 1
            ReDim Preserve sArray(1 To i)
            sArray(i) = ws.UsedRange.Address(ReferenceStyle:=XlReferenceStyle.xlR1C1, external:=True)
        End If
    Next ws
    If i = 0 Then Exit Sub

    '---Consolidate using the Array
    Sheets("Sheet1").Range("A1").Consolidate Sources:=(sArray), _
        Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
End Sub

这篇关于在VBA中创建具有多个源的Excel合并工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！