Cut,Copy and Paste

Cut, Copy and Paste are those actions which are very commonly used. In this chapter you will learn how to use Cut, Copy, Paste and Paste Special, Insert Cut cells and Insert Copied cells.

Table of Contents

Cut and Paste

We can cut the cells the cells or range and can paste on another place by using the VBA. Cut property of the range is used to do so.

In the below image we are cutting Sales data from “C4:C112” and pasting on “F4”

Sub Cut_Paste()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("C4:C12").Cut Destination:=sh.Range("F4")

End Sub

Copy and Paste

We can copy the cells or range and can paste on another place.

In the below image we are copying the used range (All Data) and pasting on “F1”

Sub Copy_Paste()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.UsedRange.Copy Destination:=sh.Range("F1")

sh.Range("F1:H1").EntireColumn.AutoFit

End Sub

Copy and paste the data into another worksheet

Below is the example for copy all the data of sheet1 to sheet2

Sub Copy_to_Another_worksheet()

Dim sh1 As Worksheet

Set sh1 = ThisWorkbook.Sheets("Sheet1")

Dim sh2 As Worksheet

Set sh2 = ThisWorkbook.Sheets("Sheet2")

sh1.UsedRange.Copy Destination:=sh2.Range("A1")

End Sub

Copy and paste the data into another workbook

We can copy all the data of sheet1 of this workbook to sheet1 of Book1

Sub Copy_to_Another_workbook()

Dim sh1 As Worksheet

Set sh1 = ThisWorkbook.Sheets("Sheet1")

Dim sh2 As Worksheet

Set sh2 = Workbooks("Book1.xlsx").Sheets("Sheet1")

sh1.UsedRange.Copy Destination:=sh2.Range("A1")

End Sub

Paste Special

We can copy a range and paste special values, formats, formulas, validations etc. by using PasteSpecial property of the range.

Sub Paste_Special()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("A1:A10").Copy

'Paste Special Values
sh.Range("D1").PasteSpecial xlPasteValues

'Paste Special Formats
sh.Range("D1").PasteSpecial xlPasteFormats

'Paste Special Formulas
sh.Range("D1").PasteSpecial xlPasteFormulas

'Paste Special Comments
sh.Range("D1").PasteSpecial xlPasteComments

'Paste Special Values and Number Formats
sh.Range("D1").PasteSpecial xlPasteValuesAndNumberFormats

'Paste Special Validation
sh.Range("D1").PasteSpecial xlPasteValidation

End Sub

We can transpose the copied data. Transpose:=True need to be used to do that.

Sub Paste_Special_Transpose()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("A1:A10").Copy

'Paste Special Values and Transpose the data

sh.Range("D1").PasteSpecial xlPasteValues, Transpose:=True

End Sub

Insert Cut cells

To cut the range and insert it between the data is commonly used.

Let’s say in below data we want to move the column C data in Column A.

Sub Insert_Cut_Cells()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("C:C").Cut

sh.Range("A1").Insert

End Sub

After executing this code column C will be moved to column A

Insert Copied cells

Let’s say in below data we want to Copy the column C data in Column A.

Sub Insert_Copied_Cells()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("C:C").Copy

sh.Range("A1").Insert

Application.CutCopyMode = False

End Sub