将工作表整合成一体 [英] Consolidating worksheets into one

问题描述

组织1

---

$我有几个excel工作表b $ b

名称职业

Chris - contracter

简 - 员工

---

组织2

---

名称职业

Bob - 设施

Trey - 软件开发人员

< hr>

并且希望将这些工作表组合成格式为

的主工作表组织名称职业

1 - Chris - contracter

1 - 简 - 员工

2 - Bob - 设施

2 - Trey - 软件开发人员

使用VBA我可以拉使用

  NumSheets = Worksheets.Count 
 
工作表（1）将所有数据导入到一个工作表中。选择
 Sheets.Add 
 ActiveSheet.Name =全部
对于X = 1到NumSheets 
工作表（X + 1）.S选择
 curAgency = ActiveSheet.Name 
 NumRows = ActiveSheet.UsedRange.Rows.Count 
行（5：&选择
 Selection.Copy 
工作表（全部）。选择
 ActiveSheet.Range（B1）。粘贴
 Selection.End（xlDown）.Select 
 ActiveCell.Offset（1，0）。选择
工作表（X + 1）。选择
范围（A1）。选择
下一个X 
工作表（所有）选择
范围（A1）。选择
 End Sub 
  

但我不确定如何插入列，并将组织名称附加到主列表中的每个组。

解决方案

我不知道我是否明白你的问题，但这可能有助于：

  Sub MergeAll（）
 Dim r As Long，ws As Worksheet，rAll As Long，wsAll As Worksheet 
 Dim i As Long 
 Worksheets.Add After：= Worksheets（Worksheets.Count）
 ActiveSheet.name =All
设置wsAll = ActiveSheet 
 rAll = 2 
对于每个ws在工作表
如果ws.name<> 所有然后
r = ws.Cells（ws.Rows.Count，1）.End（xlUp）.Row 
对于i = 1到r 
 wsAll.Cells（rAll，1） = ws.name 
 wsAll.Cells（rAll，2）= ws.Cells（i，1）
 wsAll.Cells（rAll，3）= ws.Cells（i，2）
 rAll = rAll + 1 
下一个i 
结束如果
下一个ws 
 End Sub 
  

我猜想，有一个Excel文件有多张。每个表格代表一个组织。
所以makro将运行每张表（除了Sheet的名称为全部），并将表格的数据写入全部。
我希望这是你需要的。

I have a few excel worksheets in the style of

Organization 1

---

Name Occupation

Chris - contracter

Jane - employee

---

Organization 2

---

Name Occupation

Bob - Facilities

Trey - Software Developer

---

And would like these worksheets to be combined into a master worksheet with the format

Organization Name Occupation

1 - Chris - contracter

1 - Jane - employee

2 - Bob - Facilities

2 - Trey - Software Developer

Using VBA I'm able to pull all the data into the one worksheet using

NumSheets = Worksheets.Count

Worksheets(1).Select
Sheets.Add
ActiveSheet.Name = "All"
For X = 1 To NumSheets
Worksheets(X + 1).Select
curAgency = ActiveSheet.Name
NumRows = ActiveSheet.UsedRange.Rows.Count
Rows("5:" & NumRows).Select
Selection.Copy
Worksheets("All").Select
ActiveSheet.Range("B1").Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Worksheets(X + 1).Select
Range("A1").Select
Next X
Worksheets("All").Select
Range("A1").Select
End Sub

But I am unsure how to insert the column and append the organization name to each group on the master list.

解决方案

I don't know if i understand your problem, but this could help:

Sub MergeAll()
Dim r As Long, ws As Worksheet, rAll As Long, wsAll As Worksheet
Dim i As Long
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.name = "All"
Set wsAll = ActiveSheet
rAll = 2
For Each ws In Worksheets
    If ws.name <> "All" Then
        r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        For i = 1 To r
            wsAll.Cells(rAll, 1) = ws.name
            wsAll.Cells(rAll, 2) = ws.Cells(i, 1)
            wsAll.Cells(rAll, 3) = ws.Cells(i, 2)
            rAll = rAll + 1
        Next i
    End If
Next ws
End Sub

I guessed, that there is one Excel file with multiple sheets. Each sheet represents one organization. So the makro will run through each sheets (except Sheet with name "All") and write the data of the sheets to "All". I hope that is what you need.

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