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 first part, I will explain about creating, editing, hiding, selecting and some tips.
To create a macro in excel, you just have to click on:
Sub test()
Your code here
End Sub
Alternatively, you can use the “record macro” option:
The “record macro” will create automatically the code for you based on your action in the sheet so it is very useful if you don’t have any knowledge about coding. The only point is that sometimes, it is not working properly and in this case, you need to get dirty your hands.
To edit/open a macro:
If you want to hide the macro, put “private” at the beginning, for instance: “private sub test()” instead of “sub test()”. In this case, you will not see it anymore in the “view macro”, to see it:
If you don’t have the “developer” option, you need to activate it:
Talking about hidden things, the following code allows to run the macro for the hidden sheet:
Sub test()
' Change Sheet3 by yours
Application.ScreenUpdating = False
Worksheets("Sheet3").Visible = True
' Put the code here
Worksheets("Sheet3").Visible = False
Application.ScreenUpdating = True
End Sub
To select a sheet, there are 2 options but the result is the same:
Sub test()
' Worksheets = Sheets, for instance Worksheets("Sheet1") = Sheets("Sheet1")
Worksheets("Sheet1").Select
End Sub
Putting that, I am asking it to select the sheet with the name “Sheet1” and all codes below will be executed in this sheet. If the sheet has another name, for instance “SLA problem”, I will put “Worksheets("SLA problem").Select”.
The other way to select a sheet is to put its name before any actions. This one is not selecting the sheet3 but only the cell of the sheet3 then do a copy, it means that below the line, if I put more codes, the macro will execute them at the current sheet if not specify otherwise. For instance, if I am executing the macro on the sheet2, only this line will be executed on the sheet3 and the rests on the sheet2
Sub test()
Sheets("Sheet3").Range("A2").Copy
End Sub
To select the current sheet, you can use the same way as described above but also using “activesheet”. In fact, it is not quite useful because by using it, I am asking to run the code in the current sheet, meaning that if I don't put it, I get the same result but in some particular conditions, “activesheet” should be used for the code to work.
Sub test()
' same result if putting only Range("A:A").Copy
ActiveSheet.Range("A:A").Copy
End Sub
The other way to use it:
Sub test()
' instead of ActiveSheet, I can put Sheets("sheet1")
With ActiveSheet
Range("A:A").Copy
End With
End Sub
The challenge is when I have to put different macros into 1 single one because some cautions need to be taken for it to work properly. My recommendations:
For the second part, read Use a macro to copy, cut, paste, replace and delete in an excel report (part 2).
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...