Click here to Skip to main content
13,861,988 members
Rate this:
 
Please Sign up or sign in to vote.
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:
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
   
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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 



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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190214.1 | Last Updated 7 Jul 2014
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100