For most of my reportings, 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.

The easier way is to use the “record macro” because it 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. You just have to click on:

• 1. view -> macros -> record macro NOTE: if this option is greyed out, save the file in “*.xlsm” instead of “*.xlsx”

• 2. In “macro name”, put a name (for instance “test”)
• 3. In “store macro in”, select “this workbook”
• 4. Click “OK”

• 5. Work on your sheet as you get used to do
• 6. Once done, click on “stop recording”
• 7. To see the recorded macro, go to “view macro”

The only point is that sometimes, it is not working properly and in this case, you need to get dirty your hands. To create manually a macro, you just have to click on:

• 1. view -> macros -> view macro

• 2. In “macro name”, put a name (for instance “test”)
• 3. In “macros in”, select “this workbook” NOTE: to create a macro in a specific sheet, for instance sheet3, open “visual basic” in the “developer” tool then double click on the sheet to select it
• 4. Click on “create”

• 5. Put your code between “sub test()” and “end sub”

Sub test()
Your code here
End Sub
              

• 6. Once done, to test it, you can:
  • Press F5 on your keyboard to run the whole macro
  • Press F8 on your keyboard to run step by step
• 7. Once all OK, save the excel file in the “xlsm” format.

To edit/open a macro:

• 1. view -> macros -> view macro
• 2. Select the macro then click on “edit”

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:

• Press “alt + F11” on your keyboard
• Or click on “developer -> visual basic” or “developer -> view code”

If you don’t have the “developer” option, you need to activate it:

• File -> options -> customize ribbon
• On the right side, under “customize the ribbon”, select “developer”
• Click on “OK”

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

And to select a table:

Sub test()
' for the current/active worksheet
ActiveSheet.ListObjects("Table1").Range.Select
' for a specific worksheet
Sheets("Sheet1").ListObjects("Table1").Range.Select
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:

• Make sure to not use the same string, integer, etc. for different actions. For instance, I define “dim i as integer” for 2 different actions, it will not work. I have to define “dim i as integer” for the first action and “dim k as integer” for the second action. As you can see, the first one is “i” and the second one is “k”, I can put “i1” and “i2” or anything else, what I want to say, they need to be different.
• If the code is too long, put it in different lines for a better view by using “_”. For instance, the code is: Selection.Replace What:="test", Replacement:="no test" It can be: Selection.Replace What:="test", _ Replacement:="no test"
• To avoid a popup message, put this line at the end of your code: Application.DisplayAlerts = False
• To make your full code as case-insensitive (no matter if data contains lower or upper case letters), put this line at the top of the page: Option Compare Text

For the second part, read Use a macro to copy, cut, paste, replace and delete in an excel report (part 2).