在Excel中合并工作表 [英] Combine sheets in excel

问题描述

我有一个工作簿，里面有50多张纸.我想要做的是将所有工作表组合成1个符合以下条件的主表: 1.每张纸在其自己的列中 2.工作表名称作为该列的标题

I have a workbook that has 50 plus sheets in it. What I am looking to do is to combine all the sheets into 1 master sheet with the following criteria: 1. Each sheet in its own column 2. The sheet name as the header of that column

每张纸都有一列(A)，其中有数据，但行数却很多.工作表中没有标题. 从我的研究中，我发现可以将所有工作表合并为1列，但这无济于事. 任何帮助将不胜感激，谢谢您

Each sheet has one column (A) with data in it but various amount of rows. There are no headers in the sheets. From my research I have found that I can combine all the sheets into 1 column, but that does not help. Any help would be appreciated and thank you

推荐答案

这有点丑陋，但可以满足您的要求.只需将Set targetWS = Sheets("Sheet1")更改为要放入所有数据的工作表即可.

This is a little ugly but it will do what you want. Just change Set targetWS = Sheets("Sheet1") to be the sheet that you are putting all the data.

Sub combineSheets()
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim targetCol As Integer
    Dim endRow As Long

    'This is the sheet where the data will end up
    Set targetWs = Sheets("Sheet1")

    'This is the first column to start pasting into
    targetCol = 1

    'Loop through the worksheets in the workbook
    For Each sourceWs In ThisWorkbook.Worksheets

        'grab the data from each sheet, bu not the target sheet
        If sourceWs.Name <> targetWs.Name Then

            'find last row in source sheet
            endRow = sourceWs.Range("A999999").End(xlUp).Row()

            'paste data and name
            targetWs.Range(targetWs.Cells(2, targetCol), targetWs.Cells(endRow, targetCol)) = sourceWs.Range("A1:A" & endRow).Value
            targetWs.Cells(1, targetCol).Value = sourceWs.Name

            'next column
            targetCol = targetCol + 1
        End If
    Next sourceWs
End Sub

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