错误：范围类的特殊方法失败 [英] Error: Pastespecial method of range class failed

问题描述

我在这个网站上看了几个类似的问题，但似乎没有人做到这一点。
我正在尝试创建一个新的工作簿，复制其活动工作表的内容，并将其粘贴到新创建的工作簿中。以下是我使用的代码：

  Sub new_workbook（）
 
 
 Dim ExtBk作为工作簿
 Dim ExtFile As String 
 
 
列（A：N）。复制
 
 Workbooks.Add.SaveAs文件名：=输出.xls
 ExtFile = ThisWorkbook.Path& \output.xls
 
 
设置ExtBk =工作簿（Dir（ExtFile））
 ExtBk.Worksheets（Sheet1）。范围（A1）。粘贴：= xlPasteValues，操作：= xlNone 
 
 
 Application.DisplayAlerts = False 
 ExtBk.Save 
 Application.DisplayAlerts = True 
 
 End Sub 
  

我在 PasteSpecial 行与主题指定的错误。我有点困惑，因为如果我把它引导到源代码工作簿，那么这个工作是有用的。

也许我需要使用Windows（output.xls）？

解决方案

如果您只关心保存，请不要使用复制方法

  Sub new_workbook（）
 Dim wbMe As Workbook：Set wbMe = ThisWorkbook 
 Dim ws As工作表：设置ws = wbMe.ActiveSheet 
 Dim ExtBk As Workbook 
 
设置ExtBk = Workbooks.Add 
 ExtBk.SaveAs文件名：= wbMe.Path& \output.xls
 
 ExtBk.Worksheets（Sheet1）。Range（A：N）。Value = ws.Range（A：N）值
 
 Application.DisplayAlerts = False 
 ExtBk.Save 
 Application.DisplayAlerts = True 
 
 End Sub 
   ThisWorkbook 未保存，则此操作将失败（以及您的代码也将失败）。 p> 
I looked at a few similar questions on this site but it seems like none of them did the trick.
I'm trying to create a new workbook, copy the contents of its active sheet and the paste it to this newly created workbook. Below is the code I am using:
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
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.

Maybe I need to use Windows(output.xls) ?
解决方案
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
Note: this will fail (and so will your code, previously) if your ThisWorkbook is unsaved.

                        
这篇关于错误：范围类的特殊方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！