Run time eror:1004 Application-defined or object-defined error in vba

Question

0.00/5 (No votes)

See more:

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:

VB

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"))

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

Posted 6-Jul-14 19:39pm

Venkat Raghvan

Add a Solution

2 solutions

Solution 1

The code must be modified:

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

VB

Sub CopyRange(source As Range, target As Range)

    source.Parent.Select
    source.Select
    source.Copy

    target.PasteSpecial

End Sub

VB

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

VB

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

Posted 6-Jul-14 22:09pm

Sergey Vaselenko

Comments

Maciej Los 7-Jul-14 4:23am

Sergey, the worst programming practice within VBA is to use Select method. Please, improve your answer to avoid down-voting!

Sergey Vaselenko 8-Jul-14 11:13am

Select is required for some types of copy/paste operations.
Especially if we have the PasteSpecial method.

Maciej Los 8-Jul-14 13:22pm

What i'm trying to tell you is that, that the Select method is not required, even with PasteSpecial method. Select method starts several events, among others: OnScreenUpdating, which causes the time of code execution is going slow down.
Please see my answer to find out that the Select method is not required when you use 'contextual code'.
Cheers,
Maciej

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-07-06T22:40:00

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

VB

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:

VB

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:

VB

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

I'd suggest to read this:
Visual Basic Coding Conventions[^]
Excel VBA Performance Coding Best Practices[^]