将Excel数据从多个工作表复制到一个工作表中 [英] Copying Excel data from multiple worksheets into one single sheet

问题描述

我尝试在互联网上搜索该问题的各种答案，但找不到正确的答案.我有一个Excel工作簿，其中的工作表代表每月的每一天.在每个工作表中，格式都相同(周六和周日除外)，并且工作表中包含通话统计信息.它以以下格式显示:

I have tried searching the internet for various answers to this question but cannot find the right answer. I have an Excel Workbook with worksheets represent each day of the month. In each of these sheets the format is the same (except on Saturdays and Sundays) and the sheets contain call stats. It is presented in the following format:

00:00 00:30 0 4 6 3 4 8 0 1 0 0 0

00:00 00:30 0 0 2 7 4 1 0 0 3 3 0

00:00 00:30 7 0 7 5 2 8 6 1 7 9 0

00:00 00:30 0 4 6 3 4 8 0 1 0 0 0

00:00 00:30 0 0 2 7 4 1 0 0 3 3 0

00:00 00:30 7 0 7 5 2 8 6 1 7 9 0

我需要将此数据复制到一张列出所有数据的单张纸中.基本上，它将新数据附加到旧数据的底部.因此，这将是一大清单.

I need to copy this data into 1 single sheet that lists all the data. Basically it appends the new data on to the bottom of the old data. So it will be one big list.

这怎么办?我所能看到的是如何通过将所有值加在一起来从多个数据中求和.我只需要将数据列为一个大列表即可.

How can this be done? All I can see is how to produce a total from multiple data by adding all the values together. I just need to list the data as one big list.

推荐答案

大规模

与上一次与Iain聊天时一样，已经设置了正确的参数.我删除了最后几个代码段，因为它们不太正确.如果仍然有兴趣，请检查编辑历史记录.

As with last chat with Iain, the correct parameters have been set. I have removed the last few code snippets as they are quite not right. If anyone is still interested, please check the edit history.

希望这是最终的编辑. ;)

Hopefully, this is the final edit. ;)

因此，所需的正确条件是:

So, the correct conditions needed are:

1. 工作表中的月份名称.我们为此使用了一个输入框.
2. 我们检查行数.有三个条件:总共157行，总共41行以及所有其他条件.

下面的子例程可以解决问题.

The following subroutine will do the trick.

Sub BlackwoodTransfer()

    Dim Summ As Worksheet, Ws As Worksheet
    Dim ShName As String
    Dim nRow As Long

    Set Summ = ThisWorkbook.Sheets("Summary")
    ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow"
    'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name.

    Application.ScreenUpdating = False

    For Each Ws In ThisWorkbook.Worksheets
        If InStr(1, Ws.Name, ShName) > 0 Then
        'Starting from first character of the sheet's name, if it has November, then...
            nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1
            '... get the next empty row of the Summary sheet...
            Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row
            '... check how many rows this qualified sheet has...
                Case 157
                '... if there are 157 rows total...
                    Ws.Range(Cells(57,1),Cells(104,13)).Copy
                    '... copy Rows 57 to 104, 13 columns wide...
                    Summ.Range("A" & nRow).PasteSpecial xlPasteAll
                    '... and paste to next empty row in Summary sheet.
                Case 41
                    Ws.Range(Cells(23,1),Cells(126,13)).Copy
                    Summ.Range("A" & nRow).PasteSpecial xlPasteAll               
                Case Else
                    Ws.Range(Cells(23,1),Cells(30,13)).Copy
                    Summ.Range("A" & nRow).PasteSpecial xlPasteAll
            End Select
        End If
    Next Ws

    Application.ScreenUpdating = True

End Sub

@Iain:签出注释，并将其与MSDN数据库交叉引用.那应该解释每个功能/方法的确切作用.希望这会有所帮助！

@Iain: Check out the comments and cross reference them with the MSDN database. That should explain what each function/method is doing exactly. Hope this helps!

这篇关于将Excel数据从多个工作表复制到一个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！