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”:
' copy cell A2 of the current sheet
' to cut, replace Copy by Cut
' cut cells A2:B10 of Sheet2
' to copy, replace Cut by Copy
Then to paste, add “Range()” corresponding to the cell where to paste:
' copy cell A2 of the current sheet and paste it to cell D5
' cut cells A2:B10 of Sheet2and paste them to the current sheet in cell A2
' copy cell A2 of Sheet1and paste it to the cell A2 of Sheet3
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:
' I put it in 2 lines because in 1 single line, I will get an error
To delete the content of a cell:
' clear all values in cells B2:B5
To delete the entire cells, not only the contents:
' delete the cells B2:B5 and automatically move the other cells to left or up
' delete the cells A2:A5 and move the other cells up
' delete the cells A2:A5 and move the other cells to left
To delete columns and rows:
' deleting columns A and E to G
' deleting rows 1 and 5 to 6
To replace a content of a cell by another value:
' replace old by new for the column A
Range("A:A").Replace What:="old", Replacement:="new"
To insert columns and rows:
' insert a new column in column A
' insert a new row in the row 4
For the second part, read Use a macro to create, edit, hide and select in an excel report (part 1).