Expand Minimize

Range.Copy Method (Excel)

Copies the range to the specified range or to the Clipboard.

expression .Copy(Destination)

expression A variable that represents a Range object.

Parameters

Name

Required/Optional

Data Type

Description

Destination

Optional

Variant

Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.

Return Value

Variant

The following code example copies the formulas in cells A1:D4 on Sheet1 into cells E5:H8 on Sheet2.

Worksheets("Sheet1").Range("A1:D4").Copy _ 
    destination:=Worksheets("Sheet2").Range("E5")


Sample code provided by: MVP Contributor Bill Jelen, MrExcel.com | About the Contributor

The following code example inspects the value in column D for each row in Sheet1. If the value in column D equals "A" the entire row is copied onto SheetA, in the next empty row. If the value equals "B" the row is copied onto SheetB.

Public Sub CopyRows() 
    Sheets("Sheet1").Select 
    ' Find the last row of data 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row 
    For x = 2 To FinalRow 
        ' Decide if to copy based on column D 
        ThisValue = Cells(x, 4).Value 
        If ThisValue = "A" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetA").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        ElseIf ThisValue = "B" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetB").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        End If 
    Next x 
End Sub 
 

MVP Bill Jelen is the author of more than two dozen books about Microsoft Excel. He is a regular guest on TechTV with Leo Laporte and is the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

Concepts

Other resources

Show:
© 2014 Microsoft