Hello everybody welcome to the third dashboard. In this video we will see how to create a macro where if we change the data from the given drop down like HR, it should automatically change the data report from the second pivot into the chart and should give me the relevant result in which turn will reflect on to my chart to do so, the first thing is as already mentioned, we have to create a macro but the file that we are using is not a macro enabled file. So whatever recording we do will not be stored to do so save this file as a macro enable. So that is dashboard three and I'll change the type of Excel file as macro enabled file once done I'll save it so now whatever macro I create will be stored into this Excel file. Let's do the macro. So first, the macro I need to record is the changing of the future.
Filter in this pivot. So I'll go to View tab macro, record a macro, just name it pivot filter. So once the name is done we click on OK to understand whether the macro is started recording at the bottom there's a stop icon. Apart from that if we go to the View tab macro, it says stop recording, that means the recording has started. So the recording that I'll be planning to do is just go to the drop down and select any other parameter. For the current example I'm taking ID and there it is.
Once done I stopped the macro. We can also continue the macro to navigate from sheet to sheet. So whatever steps needed just simply use the recording and get the codes. Say for example, I record another macro saying switch sheets where I just switch between the sheets. So that will give me an understanding an idea that what code has to be written down now if I wish to just switch between the sheets, so there it is just stop the recording. Now let's see the macro.
To view the recording, simply go to View tab macro. So there are two macros that we have just created, and just click on one of them and click on Edit and automatically we see a VBA editor that is the Visual Basic application editor which is for Excel, we can see all the codes that's needed to switch between the sheets the codes is selection, and under double quotation the sheet name and then select enter change the data from the filter that is in a pivot they are using the given tags. This active sheet pivot the pivot name and what type and I'm actually filtering the job type and what I've selected. In the next video, we will see how to automate this process in such a way where I can use the drop down which will automatically reflect on the charts. See you in the next Video.
Keep learning. Keep practicing. Take care.