Use a macro to copy, cut, paste, replace and delete in an excel report (part 2)

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
macro vba excel macro vba excel
macro vba excel macro vba excel

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
macro vba excel macro vba excel

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:

macro vba excel

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

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
macro vba excel macro vba excel

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
macro vba excel macro vba excel

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
macro vba excel macro vba excel

To replace a content of a cell by another value:

Sub test()
' replace old by new for the column A
Range("A:A").Replace What:="old", Replacement:="new"
End Sub
macro vba excel macro vba excel

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
macro vba excel macro vba excel

For the second part, read Use a macro to create, edit, hide and select in an excel report (part 1).

Interesting Management