This is the second part of my tutorial and it will be focused on copying, cutting, pasting, replacing, deleting and inserting. If you missed the first part, read Use a macro to create, edit, hide and select in an excel report (part 1).

To copy, I have to add “.Copy” and to cut, “.Cut”:

Sub test()
' copy cell A2 of the current sheet
' to cut, replace Copy by Cut 
Range("A2").Copy
' cut cells A2:B10 of Sheet2
' to copy, replace Cut by Copy 
Sheets("Sheet2").Range("A2:B10").Cut
End Sub

Then to paste, add “Range()” corresponding to the cell where to paste:

Sub test()
' copy cell A2 of the current sheet and paste it to cell D5
Range("A2").Copy Range("D5")
' cut cells A2:B10 of Sheet2and paste them to the current sheet in cell A2
Sheets("Sheet2").Range("A2:B10").Cut Range("A2")
' copy cell A2 of Sheet1and paste it to the cell A2 of Sheet3
Sheets("Sheet1").Range("A2").Copy Sheets("Sheet3").Range("A2")
End Sub

This is a simple paste, if I want to paste with the same options I have when I do it manually with the “paste” function:

I have to add a special code that I can find in the Microsoft’s webpages with their explanation:

• Paste type
• Paste special operation

For instance:

Sub test()
' I put it in 2 lines because in 1 single line, I will get an error (paste as values)
Range("A2").Copy
Range("D5").PasteSpecial (xlPasteValues)
End Sub

To put a formula:

Sub test()
'put formula in cell D2
Range("D2").Formula = "=VLOOKUP(""january"",A2:B13,2,0)"
End Sub

This code will scroll/drag down the selected cell by copying it to a specific number of rows by pasting it:

Sub test()
' for more columns, change A13 by A13:D13
Range("A13").Select
' change X by the number of row to paste
Selection.AutoFill Range(Selection.Offset(0, 0), Selection.Offset(0 + X, 0))
End Sub

To delete the content of a cell:

Sub test()
' clear all values in cells B2:B5 
Range("B2:B5").ClearContents
End Sub

To delete the entire cells, not only the contents:

Sub test()
' delete the cells B2:B5 and automatically move the other cells to left or up
Range("B2:B5").Delete
' delete the cells A2:A5 and move the other cells up
Range("A2:A5").Delete Shift:=xlUp
' delete the cells A2:A5 and move the other cells to left
Range("A2:A5").Delete shift:=xlToLeft
End Sub

To delete columns and rows:

Sub test()
' deleting columns A and E to G
Range("A:A,E:G").EntireColumn.Delete
' deleting rows 1 and 5 to 6 
Range("1:1,5:6").EntireRow.Delete
End Sub

To replace a content of a cell by another value:

Sub test()
' replace old by new for the column A
' optional add requirements like case sensitive, exact word, etc.
' i.e. Range("A:A").Replace What:="old", Replacement:="new", LookAt:=xlWhole
Range("A:A").Replace What:="old", Replacement:="new"
End Sub

For requirements, check the Microsoft Range.Replace method to see all options.

To find a value in a cell:

Sub test()
Dim Cell As Range
Range("A:A").Select
' search the word old in Column A
Set Cell = Selection.Find(What:="old", MatchCase:=False, SearchFormat:=False)
If Cell Is Nothing Then
  Range("B2") = "no"
Else
  Range("B2") = "yes"
End If
End Sub

For requirements, check the Microsoft Range.Find method to see all options.

To insert columns and rows:

Sub test()
' insert a new column in column A 
Range("A:A").EntireColumn.Insert
' insert a new row in the row 4
Range("4:4").EntireRow.Insert
End Sub