VBA运行时错误1004在ActiveSheet.Paste

Question

这个错误已经困扰我一段时间了，虽然运行宏单独，他们总是工作。由于单独运行10个宏有点麻烦，我决定将它们编译成一个模块，并且可以在单击表单按钮时运行。这样做之后，我一直得到这个错误在活动表。粘贴所有宏。任何帮助都会很感激。谢谢你。

Sub ItemCode()

'
' AutoFilter Macro
' Scan itemCode
Dim WB As Workbook
Set WB = ActiveWorkbook
ActiveSheet.Range("C1").AutoFilter Field:=3, Criteria1:=Array("#DIV/0!", "#N/A", "#Ref", "Null"), Operator:=xlFilterValues
If Range("C1").End(xlDown).Value = "" Then
Call CustCode
Else
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("SampleFile.xlsx").Activate
ActiveWorkbook.Sheets.Add.Name = "ItemCode"
Worksheets("ItemCode").Activate
ActiveSheet.Paste
Windows("ABF_Sample.xlsx").Activate
Application.CutCopyMode = False
Selection.AutoFilter


Application.StatusBar = "20% Completed"

End If
End Sub

注意:SampleFile.xlsx在另一个宏中声明。

Answer 1

试试下面的代码。您需要更改代码中的工作簿和工作表名称以适应。最好声明工作簿和工作表名称，以避免将值粘贴到错误位置的问题。这也意味着你不必激活工作簿和工作表，这确实会减慢你的代码。

Sub ItemCode()


'
' AutoFilter Macro
' Scan itemCode
Dim WB1 As Workbook
Set WB1 = Workbooks("ABF_Sample")

Dim WS1 As Worksheet
Set WS1 = WB.Sheets("Sheet1")

Dim FilePath As String
Dim Filename As String

FilePath = "enter your file path here"
Filename = "SampleFile"

Dim WB2 As Workbook
Set WB2 = Workbooks.Add
ActiveWorkbook.SaveAs (FilePath & Filename & ".xlsx")

WS1.Range("C1").AutoFilter Field:=3, Criteria1:=Array("#DIV/0!", "#N/A", "#Ref", "Null"), Operator:=xlFilterValues
If WS.Range("C1").End(xlDown).Value = "" Then
Call CustCode
Else
WS1.UsedRange.Copy
With Workbooks(Filename)
.Sheets.Add.Name = "ItemCode"
.Sheets("ItemCode").Range("A1").PasteSpecial Paste:=xlPasteValues
End With

Application.CutCopyMode = False
Selection.AutoFilter

Application.StatusBar = "20% Completed"
End If
End Sub