Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
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.
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
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
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
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
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
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
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
After executing this code column C will be moved to column A