For most of my reporting, I am using a vba or a macro because I like to automatize at least 99% of my reports. For me, there are 2 advantages, the time and the accuracy. For example, if I am doing manually a report, it may take minimum 1 hour and I may make some mistakes, of course, it will depend of the size of the data. For the same report, with a macro, it will take maximum 3-5 minutes and no errors.

The disadvantage is the effort at the beginning because creating a macro to do my report automatically, it is not an easy task. I need to code then to test and if I got an error, I need to fix it then to test again until I will get the result I want.

To resume, it is very a time consuming at the beginning but once I have it, it will speed everything in the future so if you want to use the macro, don’t think about short term, most about long term. As I said before, I use a vba in most of my reports, meaning that there are some reports that I don’t use it so privileging the formula option or simply, in the old fashion, manually.

The purpose of this topic is not to explain you everything, there are books that will explain better than me. Here, I will just give you an introduction and some simple codes that I am using most of the time dividing in 2 parts. For this one, I will explain about copying, cutting, pasting, replacing, deleting and inserting.

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
Range("A2").Copy
Range("D5").PasteSpecial (xlPasteValues)
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 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

If you missed the first part, read Use a macro to create, edit, hide and select in an excel report (part 1).