MS Excel ActiveSheet.Paste导致错误1004，方法失败 [英] MS Excel ActiveSheet.Paste results in Error 1004, Method failed

问题描述

我有一个项目。我将在最后给出项目的描述。现在，足以说，我的代码遇到了问题。

宏快捷键是CTRL + Shift + N.当前的问题是ActiveSheet.Paste命令无法正常工作。我做了一些研究并发现这种情况发生了，您可能会尝试重新启动工作簿。我做到了。问题始于第一个粘贴命令。



我不确定该项目的另一个方面是循环。我希望这个循环基于在东部试验平衡工作表中找到F列中有NEW_ACCT的任何其他记录。我不知道如何编写该循环。



现在有关项目的描述...



我有一本工作簿。我想为这个项目使用六个工作表。第一个是名为East Trial Balance的参考工作表。此工作表从数据库的A列到D列填充。在F列中是一个公式，它在工作表Tax Basis BS East中查找B列中的值，该值等于East Trial Balance中A列的值。如果没有，则出现单词NEW_ACCT。

使用它，我想循环遍历东部试算表中F列中有NEW_ACCT的任何和所有记录，我想从列中添加值A和C到Tax Basis BS East工作表。

这是一个棘手的部分...

要添加的记录必须进入其名称与East Trial Balance上的D列值相匹配的部分。它必须通过搜索列A找到Tax Basis BS East中的匹配部分。然后必须将光标向上移动一行然后插入一个新行。这样我就不会失去我的公式。然后在Tax Basis BS East的A栏中，添加East Trial Balance的C栏和Tax Basis BS East的B栏中的值，添加East Trial Balance A列中的值。

然后我需要将此新记录上方行C到N中存在的公式复制到新记录的相同列。

尚未完成...

在继续下一个新记录之前，我想重复工作表的附加部分Tax Basis BS Other，Tax Basis BS SL，Tax Basis BS VIE和Tax Basis BS West。



这是我迄今为止开发的代码...

I have a project. I will give the description of the project at the end. For now, suffice to say, I am running into problems with my code.
The macro shortcut keys are CTRL+Shift+N. The current problem is that the ActiveSheet.Paste command is not working. I did some research and read that this happens and that you might try re-starting the workbook. I have done that. The problem starts with the first paste command.

One other aspect of the project that I am unsure of is a loop. I want this to loop based upon finding any other records in the East Trial Balance worksheet that have NEW_ACCT in column F. I am not sure how to write that loop.

Now for the description of the project...

I have a workbook. There are six worksheets I want to use for this project. The first is the reference worksheet called East Trial Balance. This sheet gets populated in columns A thru D from a database. In column F is a formula that looks for a record in worksheet Tax Basis BS East for a value in column B that equals the value of column A in the East Trial Balance. If there is none, the word NEW_ACCT appears.
Using that, I want to loop through any and all records in the East Trial Balance that have NEW_ACCT in column F and I want to add the values from columns A and C to the Tax Basis BS East worksheet.
Here is the tricky part...
The record to be added must go into the section whose name matches the value of column D on the East Trial Balance. It must find the matching section in the Tax Basis BS East by searching column A. It must then move the cursor up one row and then insert a new row. This way I do not loose my formulas. Then in column A of the Tax Basis BS East, add the value from column C of the East Trial Balance and in column B of the Tax Basis BS East, add the value from column A of the East Trial Balance.
Then I need to copy the formulas that exist in columns C through N of the row above this new record to the same columns for the new record.
Not done yet...
before proceeding to the next record that is new, I want to repeat the append portion for worksheets Tax Basis BS Other, Tax Basis BS SL, Tax Basis BS VIE, and Tax Basis BS West.

Here is the code I have developed to date...

Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    Dim strSection As String
    Dim intRowA As String
    Dim intRowB As String
    
    ' Go to Trial Balance and search for a New Account
    Sheets("East Trial Balance").Select
    Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ' Move from column F to column D to get the name of the Section for the New Account
    ActiveCell.Offset(0, -2).Select
    strSection = ActiveCell.Value
    
    ' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' Move from column D to column A to get the Account Number
    Sheets("East Trial Balance").Select
    ActiveCell.Offset(0, -3).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ' Move to the column B and paste the copied Account Number
    Sheets("Tax Basis BS-SL").Select
    ActiveCell.Offset(0, 1).Select
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowB).Select
    ActiveSheet.Paste
    
    ' Go back to the Trial Balance and get a copy of the Account Name from column C
    Sheets("East Trial Balance").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 2).Select
    Selection.Copy
    
    ' Return to the Tax Basis BS and paste the Account Name
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-2, 0).Select
    intRowA = "A" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-West").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy

    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-West").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-Other").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy

    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-Other").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-VIE").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy

    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-VIE").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste

    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-East").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy

    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-East").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste

    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

再次代码在第一个ActiveSheet.Paste失败。

其次我需要帮助编写允许此代码的循环对于在F列中具有NEW_ACCT的East Trail Balance中的任何记录重复。

Again the code fails at the first ActiveSheet.Paste.
Second I need help writing the loop that will allow this code to repeat for any record in the East Trail Balance having NEW_ACCT in column F.

推荐答案

该行为可能有多种原因。您可以尝试的一件事是使用 PasteSpecial 而不是粘贴。因为你粘贴了一个范围，它可以更好地为你工作。请参阅 Range.PasteSpecial Method [ ^ ]



另一件事是，如果目标范围的选择大小与源范围不同，则会导致问题。



还可以尝试使用调试器查看范围并正确完成复制。

There could be several reasons for the behaviour. One thing you could try is to use PasteSpecial instead of Paste. Since you''re pasting a range, it could work better for you. Refer to Range.PasteSpecial Method[^]

Another thing is that if the selection for target range is differently sized than the source range, it would cause problems.

Also try using the debugger to see the ranges and that the copying is done properly.

这篇关于MS Excel ActiveSheet.Paste导致错误1004，方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！