如何将活动工作表的内容复制到新工作簿? [英] How to copy the contents of the active sheet to a new workbook?

问题描述

我正在尝试将活动工作表的内容复制到新工作簿中.

I'm trying to copy the contents of the active sheet to a new workbook.

Sub new_workbook()

    Dim ExtBk As Workbook
    Dim ExtFile As String

    Columns("A:N").Copy

    Workbooks.Add.SaveAs Filename:="output.xls"
    ExtFile = ThisWorkbook.Path & "\output.xls"

    Set ExtBk = Workbooks(Dir(ExtFile))
    ExtBk.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    Application.DisplayAlerts = False
    ExtBk.Save
    Application.DisplayAlerts = True

End Sub

我在 PasteSpecial 行出现错误，并在主题上指定了错误.我有些困惑，因为如果我将其定向到源工作簿，这会起作用.

I'm getting an error at the PasteSpecial line with the error specified at the subject. I'm a bit confused since this works if I direct it to the source workbook.

也许我需要使用Windows(output.xls)?

Maybe I need to use Windows(output.xls)?

推荐答案

如果只考虑保存值，则根本不使用 Copy 方法.

Don't use Copy method at all if you're only concerned with saving the Values.

Sub new_workbook()
Dim wbMe As Workbook: Set wbMe = ThisWorkbook
Dim ws As Worksheet: Set ws = wbMe.ActiveSheet
Dim ExtBk As Workbook

Set ExtBk = Workbooks.Add
ExtBk.SaveAs Filename:=wbMe.Path & "\output.xls"

ExtBk.Worksheets("Sheet1").Range("A:N").Value = ws.Range("A:N").Value

Application.DisplayAlerts = False
ExtBk.Save
Application.DisplayAlerts = True

End Sub

注意:如果您的 ThisWorkbook 未保存，此操作将失败(以前的代码也将失败).

Note: this will fail (and so will your code, previously) if your ThisWorkbook is unsaved.

这篇关于如何将活动工作表的内容复制到新工作簿?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！