Welcome back to the third dashboard. In this video, we will see how to use these codes to dynamically alter our data in such a way that from the Excel sheet if I change the data from the dashboard, it automatically should reflect in the data for the trend chart. To do so, the code is already there. So I'll just align my screen into a different format. So I can actually work with both the Excel and VBA screen. I'll just reduce the width a little bit over here.

That should do it. Now, first thing I wish to say on the first sheet, so I'll be creating a sample over here. So I'll just use subspace, sample bracket, open close Enter. Now I'm just replicating what is written over here, nothing new to it. Now the first task is As we can see, when I click on a cell the tag that appears is range g4 then select. So I wish that my cursor should be in the cell v2, when I'm in the sheet, that is dashboard, I do have the code that helps me to switch into the dashboard.

So I'll copy the code and I'll paste it in. There it is. So the first task that happens is I'll be on the dashboard sheet. And then I wish my cursor should be on B 12. So I'll copy the code over here. And I'll just keep it as B 12.

Capital, small does not matter, but still just doing now to check this code. Let's give it a try. So I'll be on another sheet and I'll keep the cursor in the code and I'll use a play button or the shortcut for that is f5. So when I do that, it runs the code and I can see the output and if we're on any other sheet for selects the dashboard and the cursor is brought to To be to get my cursor somewhere else, still the process will remain the same. Once that is done, it has to take this particular data that is HR or whatever value has been selected. And it has to go into the other sheet and get the work done over there as well.

So to assign the value of the range B to we'll be using a container so we'll name it as value or rather. So we'll name this as job type. So I'll just say use a job tag just to make it more relevant. Now. Now this is something that the user will be selecting. So I'll be assigning that with the content selected and B to not should not be selected rather, the value has has to be given.

So I'll just say value. And there it is. That means whatever the value has been selected by the user in v2 will be given to user job type. So let's execute it. So when I play this currently, you cannot see the value because it's already executed completely. So let me use another trick.

So I'll say that assign these values to another cell. So I'll just say select v 20 and it should be given with the value which is selected. So now when I execute this using f5, you can see HR is taken down in cell 20. So if I say something else, say for example, finance and after I execute this particular code automatically be 20 will give out the value finance So there it is. That means the container user job type has the value selected by the user. Now, once the value is taken, I'll just delete this code.

So once the value is taken into this particular container, it should go into the second sheet, then change the filter rather than ID, that should take the value taken by the user. So what I'll be doing is I'll copy the entire code. So I'll just take this code which is needed, okay, the code that I need is second last line that is active sheet dot pivot. And I'll paste it over here. That should do it. Okay.

Currently, it's red, because the code is incomplete. Now what it has to be converted the job type has to be the one whatever is the user Selecting and we add a container over here which has that value. So I'll just take the container and paste it just in front of the current page job type current page. But the problem is this macro will not execute, because it will try to search for the this pivot in the current sheet. So let's give it a try. So when I play, you can see the debug.

Now the problem is with this particular line as the particular code has to be executed in the sheet where the pivot is and when the pivot is created in data for the trend chart, so first, I have to put a code and that should take me to that particular sheet. So I'll use the code that I have already and I'll just copy and paste and just change the name over here with the sheet name. That's it should do it. So one The code is done, it has to come back to the sheet. And there it is. So once the code gets executed, it will come back to the dashboard sheet, and it will keep the cursor back at v2.

So let's give it a try. So now if I select marketing, keep the cursor in play the code, there it is. It goes with marketing. And if I select some other parameters like finance, keep the cursor in execute it and I get the finance. Now as you can see, the screen is flickering. So I do not want that.

So I'll be using an extra code. Now this is something we cannot get using the recording. So I'll be helping here with the code. That is app that's application. And once you write app, just simply press Control space and that brings up a list from where we can select the application, just put in a dot now automatically the additional feature will appear. That is one We are looking for is the screen update and screen update should be false.

That means the screen should not be updated when this particular code gets executed. I'll copy this particular code once again and I'll paste it and now I will select it as true. That means when the codes are executed and it's done it should update itself. Now, if I select another parameter, let's say am is and I execute the code this The screen will not be having the flipper effect. But rather I can see the update happening. In the next video we will see how to code this particular program in such a way that as I change this option, it should automatically execute this code entirely, rather than me keeping the cursor and playing it again and again.

Till the end. Keep practicing. See you in the next video. Thank you