从多个选项卡中的单个单元格中提取数据

I am not a savvy VBA user, but I believe this question is possible. I am attempting to compile data from multiple worksheets (100+) in an Excel workbook into a "summary worksheet". Each worksheet is identical and the data would be pulled from the same cell in each worksheet onto the same cell in the summary.

我不是一个精明的VBA用户,但我相信这个问题是可能的。我试图将Excel工作簿中的多个工作表(100+)中的数据编译为“摘要工作表”。每个工作表都是相同的,数据将从每个工作表中的同一单元格中提取到摘要中的同一单元格中。

Is there a code that will do this for me while also being able to exclude certain other worksheets? If so, what is the code?

是否有代码可以为我这样做,同时还能够排除某些其他工作表?如果是这样,代码是什么?

1 个解决方案

#1

Dim ws As Excel.Worksheet
Dim TargetSheet As Excel.Worksheet
Dim iIndexas Long
Dim lRow As Long
Dim strValue as string

    lRow = 1

    'Loop each worksheet in the workbook
    For iIndex = 1 To Application.Worksheets.count

        'Get the value from this worksheet
        Set ws = Application.Worksheets(iIndex)
        ws.Activate

        'Check if the name has template in it
        if instr(ucase(ws.name), "TEMPLATE") = 0 then

            strValue = ws.Range("B4").Value

            'Switch to the target sheet
            Set TargetSheet = ActiveWorkbook.Sheets("SummarySheetName")
            TargetSheet.Activate

            'Write the value
            TargetSheet.Range("C" & lRow).Value = strValue

            'Increment your row
            lRow = lRow  + 1

        End if

    Next iIndex