想实现一个功能就是：选中待合并的Excel表格所在的文件夹，将文件夹内所有非空的Excel表格复制粘贴到同一个工作薄中的不同工作表。请问如何实现？

简单写了一个半生不熟的雏形，能够实现本要求，但不容错，不完善，需要改进调整地方很多。自己消化完成。过程中有问题再说。
Option Explicit
Dim EXAPP As Excel.Application
Dim sourceWB As Excel.Workbook
Dim destWB As Excel.Workbook
Dim sourceSHT As Excel.Worksheet
Dim destSHT As Excel.Worksheet

Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Dim destcount As Integer

Set EXAPP = CreateObject("excel.application")
Set destWB = EXAPP.Workbooks.Open(Text1.Text)
destcount = destWB.Worksheets.Count

For i = 0 To File1.ListCount - 1

  Set sourceWB = EXAPP.Workbooks.Open(Dir1.Path & "\" & File1.List(i))
    For j = 1 To sourceWB.Worksheets.Count
      Set sourceSHT = sourceWB.Worksheets(j)
      
      sourceSHT.Copy After:=destWB.Sheets(destcount)
      destcount = destcount + 1
    Next
Next
End Sub

Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub

Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub

Private Sub Form_Load()
File1.Pattern = "*.xlsx"
End Sub