excel - 如何将除工作表 1 之外的每个工作表另存为单独的工作簿？

Question

我有一个包含 5 个工作表的 Excel 工作簿，但我只想将工作表 2 保存到工作表 - 5，但我不想保存工作表 1。我想将其排除在保存之外。我该怎么做？

我尝试了一些代码，但遇到了困难。

Sub SaveShtsAsBook()

    Dim xcsvFile As String
    Dim datestring As String
    Dim Count As Integer


    datestring = DateValue(Now) & Time
    datestring = Replace(datestring, "/", "_")
    datestring = Replace(datestring, ":", "_")
    datestring = Replace(datestring, " ", "_")

'    Application.WindowState = xlMinimized
'    Application.Visible = False

    Application.EnableEvents = True
   ' Application.Calculation = xlCalculationManual
  '  Application.Wait (Now + TimeValue("0:00:10"))

    For Count = 1 To 3000
    DoEvents
    Next Count


    'For Each Sheet In Worksheets
    For Each Sheet In ThisWorkbook.Worksheets ' Safer way to qualify the worksheets with the workbook where this code lies

       Select Case Sheet.Name
        Case "Sheet1"
        ' do nothing

        Case Else
           xcsvFile = "E:\" & xWs.Name & "_" & datestring & ".csv"
       ' xcsvFile = "E:\" & "\" & xWs.Name & ".csv" 'compare mine to yours to see issues

        xWs.Copy

        Dim newSheet As Workbook 'setting copied sheet to workbook variable for easier coding
        Set newSheet = ActiveSheet.Parent 'parent of worksheet is workbook

        newSheet.SaveAs Filename:=xcsvFile, FileFormat:=xlCSV, CreateBackup:=False
        newSheet.Close False
       End Select
    Next
End Sub

Answer 1

问题是您引用的 xWs 变量在您的代码中不存在。如果您使用 Sheet 更改它，它会完美地工作，正如我在 Excel 上测试的那样:

Sub SaveShtsAsBook()
    Dim xcsvFile As String
    Dim datestring As String
    Dim Count As Integer

    datestring = DateValue(Now) & Time
    datestring = Replace(datestring, "/", "_")
    datestring = Replace(datestring, ":", "_")
    datestring = Replace(datestring, " ", "_")

'    Application.WindowState = xlMinimized
'    Application.Visible = False

    Application.EnableEvents = True
   ' Application.Calculation = xlCalculationManual
  '  Application.Wait (Now + TimeValue("0:00:10"))

    For Count = 1 To 3000
        DoEvents
    Next Count


    'For Each Sheet In Worksheets
    For Each Sheet In ThisWorkbook.Worksheets ' Safer way to qualify the worksheets with the workbook where this code lies

        Select Case Sheet.Name
            Case "Sheet1"
            ' do nothing

            Case Else
            xcsvFile = "E:\" & Sheet.Name & "_" & datestring & ".csv"
            ' xcsvFile = "E:\" & "\" & xWs.Name & ".csv" 'compare mine to yours to see issues

            Sheet.Copy

            Dim newSheet As Workbook 'setting copied sheet to workbook variable for easier coding
            Set newSheet = ActiveSheet.Parent 'parent of worksheet is workbook

            newSheet.SaveAs Filename:=xcsvFile, FileFormat:=xlCSV, CreateBackup:=False
            newSheet.Close False
        End Select
    Next
End Sub

希望这有帮助。