运行时eror：1004 vba中的应用程序定义或对象定义错误 [英] Run time eror:1004 Application-defined or object-defined error in vba

问题描述

Hi
我是vb编程的新手，因为我为excel工作表复制range设计了宏。我从一个工作簿复制了很多范围到另一个工作簿。虽然这样做，我设计并调用下面给出的宏子程序：

Hi I am new in vb programming,in that i design macro for excel sheets copy range.I copied many ranges from one workbook to another.While doing this method, i design and call of macro subroutine given below:

Sub CopyRange(source As Range, target As Range)

Dim exp As Range

MsgBox "Entering in Copy module"
   Set exp = Sheets(1).Range(source)
exp.Select
exp.Copy
Sheets(2).Range(target).PasteSpecial
End Sub
'Call the subroutine
Call CopyRange(Sheets(1).Range("E2:E100"), Sheets(2).Range("A2"))

所以我想知道宏中所需的任何更改，因为它给出了运行时间错误：1004

应用程序定义或对象定义的错误。

So I want to know any changes required in macro, because it gives Run time eror:1004
Application-defined or object-defined error.

推荐答案

代码必须修改：



1.复制前选择源表。

2.以正确的方式设置范围变量。< br $>

The code must be modified:

1. Select the source sheet before copying.
2. Set range variables in correct ways.

Sub CopyRange(source As Range, target As Range)

    source.Parent.Select
    source.Select
    source.Copy

    target.PasteSpecial

End Sub

Sub CopyRange1(source As Range, target As Range)

    Dim exp As Range

    Set exp = source

    exp.Parent.Select
    exp.Select
    exp.Copy

    target.PasteSpecial

End Sub

Sub CopyRange2(source As Range, target As Range)

    Dim exp As Range

    Set exp = Sheets(1).Range(source.Address)

    exp.Parent.Select
    exp.Select
    exp.Copy

    target.PasteSpecial

End Sub

最简单的方法是使用 Range.Copy [ ^ ]方法：

The easiest way is to use Range.Copy[^] method:

Sheets(1).Range("E2:E100").Copy Sheets(2).Range("A2")

但我需要 警告 你：如果你想在工作簿之间复制数据，而不是在同一工作簿中的工作表，你的代码被剥夺了上下文。



正确的方法是：

But i need to warn you: If you would like to copy data between workbooks, not between sheets in the same workbook, your code is deprived of context.

The proper way to do it is:

ThisWokbook.Worksheets(IndexOrName).Range("E1:E100").Copy Workbooks("IndexOrShortName").Range("A1")

上面的代码行将工作簿中的数据与代码复制到另一个工作簿。

甚至更好：

Above line of code copies data from workbook with code to the other workbook.
or even better:

Dim wshsrc as Worksheet, wshdst as Worksheet

On Error Goto Err_Handler

Set wshsrc = ThisWokbook.Worksheets(IndexOrName)
Set wshdst = Workbooks("IndexOrShortName")

wshsrc.Range("E1:E100").Copy wshdst.Range("A1")

Exit_Sub:
    On Error Resume Next 'ignore errors 
    'clean up, release resources 
    Set wshsrc = Nothing
    Set wshdst = Nothing
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_Sub 

我建议读这个：

Visual Basic编码约定 [ ^ ]

Excel VBA性能编码最佳实践 [ ^ ]

这篇关于运行时eror：1004 vba中的应用程序定义或对象定义错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！