Excel多个文档如何快速进行合并,10秒搞定~

Excel 11个月前 芮和
346 0 0

芮和给大家写了一份非常简单的VBA代码,可以快速将同个目录下所有的Excel文档合并到当前文档中,先来简单看下效果吧~

简单的贴下代码,就不具体解释了,VBA教程面向有一定基础的学员,是Excel中效率最高的功能,没有之一,后续我们会根据情况添加VBA教程~

Sub 按钮1_Click()
   
  Dim path As String
  Dim FileName As String
  Dim LastCell As Range
  Dim Wkb As Workbook
  Dim WS As Worksheet
  Dim ThisWB As String
  Dim MyDir As String

  '路径
  MyDir = ThisWorkbook.path & "\"
  ThisWB = ThisWorkbook.Name
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  path = MyDir
  FileName = Dir(path & "\*.xlsx", vbNormal)
  Do Until FileName = ""
    If FileName <> ThisWB Then
      Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
      For Each WS In Wkb.Worksheets
        Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
       If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
       Else
         WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
       End If
      Next WS
      Wkb.Close False
    End If
    FileName = Dir()
  Loop
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  Set Wkb = Nothing
  Set LastCell = Nothing
  
End Sub

点击文末下载按钮,即可获得这份小插件。

今天的自定义插件就分享到这里了~

——END——

阅读原文

版权声明:芮和 发表于 2020-06-14 0:00:00。
转载请注明:Excel多个文档如何快速进行合并,10秒搞定~ | 芮和

相关文章

暂无评论

暂无评论...