时间戳

首页 > 解决方案 > VBA 宏将工作表拆分为新的工作簿

excel - VBA 宏将工作表拆分为新的工作簿

问题描述

我对 vba/macros 很陌生，我创建了一个将多个工作表拆分为新的 excel 文件的宏。但是，当我运行宏时出现 1004 错误。 我得到的错误代码

在代码行这里调试

下面是我的代码。

Private Sub CommandButton2_Click()

 Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "C\Path.xlsm" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

注意我在 sheet1 中创建的名为“Part1”的宏按钮，并希望从下一张表开始创建新文件。请帮忙.... 第 1 部分是我创建按钮的地方

标签： excelvba

解决方案

您正试图在您的代码行中插入两条路径。

由于您定义了workbookPath不需要使用的变量"C\Path.xlsm"。您需要删除"C\Path.xlsm"和插入"\"之前wSheet.Name。请参阅下面的基本代码。使用验证后将 Debug.Print(s) 注释掉。

Dim workbookPath As String: workbookPath = ThisWorkbook.Path 'ThisWorkbook is the macro enabled workbook.

Debug.Print ThisWorkbook.Path 'open the immediate window to varify to verify the path of the macro enabled workbook. 
    
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        
        ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        
        'When you save a sheet as a workbook it becomes the activeworkbook
        Debug.Print ActiveWorkbook.Path & "\"; ActiveWorkbook.Name 'use to verify the path of the new workbook.
        
        ActiveWorkbook.Close False
    Next wSheet

End Sub

推荐阅读