Copy Method Example

This example copies Sheet1, placing the copy after Sheet3.

Worksheets("Sheet1").Copy after := Worksheets("Sheet3")

This example copies the used range on Sheet1, creates a new worksheet, and then pastes the values of the copied range onto the new worksheet.

Worksheets("Sheet1").UsedRange.Copy
Set newSheet = Worksheets.Add
newSheet.Range("A1").PasteSpecial Paste:=xlValues

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