在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屋!