根据列的内容将Excel文件拆分为多个工作簿 [英] Split an excel file into multiple workbooks based on the contents of a column

问题描述

我没有使用VBA的经验，但是我认为这是唯一可行的方法.

I'm not experienced with VBA, but I think it's the only way for this to work.

我需要向每个销售团队发送报告，但不想向他们发送其他销售团队的信息.每个工作簿有多个带有不同报告的工作表，每个工作表都有一个销售团队列.

I need to send a report to each sales team, but don't want to send them the information of other sales team. There are multiple sheets per workbook with different reports which all have a sales team column.

我希望销售团队可以过滤所有工作表，并为每个团队创建一个新的工作簿.

I would like all the sheets to be filtered by sales team, and create a new workbook for each team.

感谢您的帮助.

推荐答案

我有此解决方案.
如果您需要此解决方案，请给我发送电子邮件.

I got this solution.
Just send me an email if you need this solution.

起初我得到了这种格式:

我创建以下宏代码

At first I got this format:

I create the following macro code

Option Explicit
Dim MainWorkBook As Workbook
Dim NewWorkBook As Workbook

Sub ExportWorksheet()
Dim Pointer As Long

Set MainWorkBook = ActiveWorkbook
Range("E2").Value = MainWorkBook.Sheets.Count

Application.ScreenUpdating = False   'enhance the performance
For Pointer = 2 To MainWorkBook.Sheets.Count
    Set NewWorkBook = Workbooks.Add
    MainWorkBook.Sheets(Pointer).Copy After:=NewWorkBook.Sheets(1)
    Application.DisplayAlerts = False
    NewWorkBook.Sheets(1).Delete
    Application.DisplayAlerts = True
    With NewWorkBook
        .SaveAs Filename:="C:\Users\lengkgan\Desktop\Testing\" & MainWorkBook.Sheets(Pointer).Name & ".xls" 'you may change to yours
    End With
    NewWorkBook.Close SaveChanges:=True
Next Pointer

Application.ScreenUpdating = True
Range("D5").Value = "Export Completed"

End Sub

以下是输出

Following is the output

这篇关于根据列的内容将Excel文件拆分为多个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！