☰

使用 VBA 根据列将 Excel 工作表拆分为多个工作表

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
使用 VBA 根据列将 Excel 工作表拆分为多个工作表

问题很简单，而且可能会重复。

  1. 我有一个包含大约 50 列的 Excel 工作簿
  2. 我有一个将此工作簿拆分为多个工作簿的标准列

方法如下图所示

Name    SportGoods    quantity
ABC     CRICKETBAT    10
DEF     BaseballBat   20
GHI     football      30 
MNO     gloves        10
PQR     shoes         10 
ABCD    CRICKET SHOES 10
DEFG    BaseballBat   20
GHIL    football      30 
MNOP    gloves        10
PQRS    shoes         10

我正在寻找一个宏，使我能够根据列创建多个 Excel 工作簿运动用品喜欢：

  • Excel/CSV 包含所有板球用品，如板球棒、板球鞋、手套
  • 包含所有足球物品（如足球和鞋子）的 Excel/CSV

作为输入参数，我将提供不同的板球项目、不同的足球项目。源将是一个包含约 5000 条记录的大型 Excel 数据表。

有人可以帮我制作一个宏，以便根据上述详细信息生成多个工作簿吗？

答案1

概括

这是一个简短但智能的宏。它将活动工作表上的数据拆分并保存到不同的 CSV 文件中。新创建的文件存储在名为CSV 输出与 Excel 文件位于同一位置。

VBA 宏

Sub GenerateCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

iCol = 2                                '### Define your criteria column
strOutputFolder = "CSV output"          '### Define your path of output folder

Set ws = ThisWorkbook.ActiveSheet       '### Don't edit below this line
Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)

If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder
For Each strItem In rngUnique
  If strItem <> "" Then
    ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value
    Workbooks.Add
    ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]
    strFilename = strOutputFolder & "\" & strItem
    ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
  End If
Next
ws.ShowAllData

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

将其保存在新的 VBA 模块中

理解代码

iCol = 2                               
strOutputFolder = "CSV output"

第一行是条件列。A1代表 A 列，2B 代表 B 列，以此类推。
其次，我们定义一个文件夹名称，所有 CSV 文件都应保存在该文件夹中。您还可以设置完全限定路径，例如C:\some\folder。否则 Excel 会在您的 Excel 文件位置创建一个文件夹


 Set ws = ThisWorkbook.ActiveSheet

在这里，我们将当前工作簿和工作表保存在一个变量中。这不是必须的，但由于我们要处理多个工作簿（新创建的工作簿），我建议这样做


Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True   
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)

好的，这部分做什么？首先，我们只搜索条件列中的最后一个单元格。这必须在过滤之前完成，稍后需要。然后，我们使用著名的高级过滤器方法过滤掉标准列中的所有重复值。最后，我们保存所有可见的变量中的单元格名为rngUnique


If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder

让我们看看名为的文件夹是否CSV output已存在。如果不存在，请创建一个


For Each strItem In rngUnique
  If strItem <> "" Then
  [...]
  End If
Next

现在，我们开始循环遍历变量中的所有唯一值rngUnique. 但会跳过空值


ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value

重要的一行。我们使用自动过滤方法并查看与当前唯一值匹配的所有行。旧的高级过滤器会自动取消。


Workbooks.Add
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]

这两行创建了一个新的空工作簿并从输入工作簿中复制可见的单元格


strFilename = strOutputFolder & "\" & strItem

这里我们整理了 CSV 路径。我们将当前唯一值作为文件名。扩展名CSV因为我们选择了xlCSV输出格式，所以会自动附加。
请确保您的唯一值不包含无效的文件名字符，例如，否则< > | / * \ ? "将不会创建相应的 CSV 文件


ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False

最后一步是将当前工作簿保存为 CSV 并获取变量文件名作为文件名。CSV 分隔符取决于您的区域设置分隔符。可以更改文件格式，例如制表符分隔的 CSV 或 Excel 2003 工作簿


Application.ScreenUpdating = False
Application.DisplayAlerts = False

第一行稍微加快了我们的宏，因为 Excel 不需要显示过滤的每个步骤。
第二行抑制了烦人的文件已存在提示。稍后我们再次启用这些功能

相关内容