VBA 将范围保存为工作簿

Question

我正在尝试将范围保存为单独的工作簿。我要保存的范围在 Sheet (STF) 和 Range (B1 to J lastrow) 我的代码是:

saveFile = Application.GetSaveAsFilename(InitialFileName:=address & " " & Format(Now(), "yyyy-MM-dd hh-mm-ss"), fileFilter:="Excel Workbooks (*.xlsx),*.xlsx")
Worksheets("STF").Range("B1:J" & LR).SaveAs Filename:=saveFile

然而，显然，事情并没有那么简单。它失败了，我不确定如何解决这个问题？

帮助？

Answer 1

将工作表复制到任何位置。这将创建一个包含工作表的新工作簿(新激活)。

Option Explicit

Sub extractRange()
    Dim saveFile As String, Address As String

    Address = "ABC"

    saveFile = Application.GetSaveAsFilename(InitialFileName:=Address & " " & Format(Now, "yyyy-MM-dd hh-mm-ss"), _
                                             fileFilter:="Excel Workbooks (*.xlsx),*.xlsx")
    ThisWorkbook.Worksheets("STF").Copy

    With ActiveWorkbook
        With .Worksheets("STF")
            .Columns("K:XFD").EntireColumn.Delete
            .Columns("A").EntireColumn.Delete
        End With
        .SaveAs Filename:=saveFile, FileFormat:=xlOpenXMLWorkbook
        .Close savechanges:=False
    End With
End Sub