Copy data range quickly

[BigDelGooner]

Hi there

I am trying to use the .copy destination:= method to copy a data range from one worksheet to another but I am getting an 'Application-defined or object-defined error'. When I use this method copying just one cell its fine but doesn't seem to like copying the range. Is there something wrong with the following:

Sheets("LOCKED_SecList").Range(Cells(Start_Row, Start_Column), Cells(End_Row, End_Column)).Copy Destination:=Sheets("VIEW_NoDetails").Range(Cells(Start_Row, Start_Column), Cells(End_Row, End_Column))

Really appreciate any help guys...

 

[Leith Ross]

Hello BigDelGooner,

When working with Ranges in VBA, if the Range object you are working with (Range or Cells) isn't qualified, it is assumed you mean the Range or Cells on the ActiveSheet.

I make it a habit of using Range object variables in my code to help eliminate this problem and it make more flexible. Here is an example. You should dimension your variables to help eliminate problems also.

Sub CopyTest()

    ' Thread: http://www.mrexcel.com/forum/showthread.php?t=624451
    
    Dim DstWks As Worksheet
    Dim EndColumn As Long, EndRow As Long
    Dim StartColumn As Long, StartRow As Long
    Dim SrcWks As Worksheet
    
        StartRow = 1
        StartColumn = 1
    
        EndRow = 10
        EndColumn = 1

        Set SrcWks = Sheets("LOCKED_SecList")
        Set DstWks = Sheets("VIEW_NoDetails")
    
        SrcWks.Range(SrcWks.Cells(StartRow, StartColumn), SrcWks.Cells(EndRow, EndColumn)).Copy _
        DstWks.Range(DstWks.Cells(StartRow, StartColumn), DstWks.Cells(EndRow, EndColumn))
    
End Sub

 

[BigDelGooner]

That's great, thank you very much!

The answer is always so simple, I end up over complicating things in VBA!!

 

[circledchicken]

Hi,

Alternatively to the method suggested by Leith Ross, you could use .Address after each Cells(...) part.