Good morning everyone,
By way of introduction, I am a student in economics and I have an intermediate level in Excel. I would like to ask for the community's help in order to build a macro for an assignment. I am currently following excel lectures on UDEMY but Macros are not part of the chapter so far (i.e. I have very low knowledge of Macro
)
Here is the scenario:
On the first tab, I have a table dashboard as below;
On the second tab, I have a control sheet as below:
What I would like to do, is to set manually the inspection date for each topic (Topic A and C for Q1, topic B for Q3 etc.). Then, by clicking on a macro button, I would like excel to look up in the "controls" tab, identify all the lines relating to the relevant topic (including the headers) and create a new tab called, for example "Controls to be performed in Q1" which will include all the lines from the tab "controls" relating to Topic A and Topic C. And so on for the the other topics for the other quarters (new tab for Q2 and another one for Q3).
On top of that, I would like the macro to by dynamic and to identify which Topic needs to be inspected in which quarter based on the inspection date project. A sort of "if Topic A as an inspection date Q12020, then, it will go in the tab "Controls to be performed in Q1".
I would be so grateful if you can help me to write the script
))
Many thanks in advance,
Best regards,
Ryo,
By way of introduction, I am a student in economics and I have an intermediate level in Excel. I would like to ask for the community's help in order to build a macro for an assignment. I am currently following excel lectures on UDEMY but Macros are not part of the chapter so far (i.e. I have very low knowledge of Macro
) Here is the scenario:
On the first tab, I have a table dashboard as below;
| DASHBOARD | ||
| # | Area | Inspection Date |
| 1 | Topic A | Q1 2020 |
| 2 | Topic B | Q3 2020 |
| 3 | Topic C | Q1 2020 |
| 4 | Topic D | Q2 2020 |
| 5 | Topic E | Q3 2020 |
On the second tab, I have a control sheet as below:
| Ref | Area | Description | Control to be performed | Status |
| A01 | Topic A | [free text] | [free text] | [free text] |
| A02 | Topic A | [free text] | [free text] | [free text] |
| A03 | Topic A | [free text] | [free text] | [free text] |
| B01 | Topic B | [free text] | [free text] | [free text] |
| B02 | Topic B | [free text] | [free text] | [free text] |
| B03 | Topic B | [free text] | [free text] | [free text] |
| B04 | Topic B | [free text] | [free text] | [free text] |
| C01 | Topic C | [free text] | [free text] | [free text] |
| C02 | Topic C | [free text] | [free text] | [free text] |
| C03 | Topic C | [free text] | [free text] | [free text] |
| C04 | Topic C | [free text] | [free text] | [free text] |
| C05 | Topic C | [free text] | [free text] | [free text] |
| D01 | Topic D | [free text] | [free text] | [free text] |
| D02 | Topic D | [free text] | [free text] | [free text] |
| E01 | Topic E | [free text] | [free text] | [free text] |
| E02 | Topic E | [free text] | [free text] | [free text] |
| E03 | Topic E | [free text] | [free text] | [free text] |
What I would like to do, is to set manually the inspection date for each topic (Topic A and C for Q1, topic B for Q3 etc.). Then, by clicking on a macro button, I would like excel to look up in the "controls" tab, identify all the lines relating to the relevant topic (including the headers) and create a new tab called, for example "Controls to be performed in Q1" which will include all the lines from the tab "controls" relating to Topic A and Topic C. And so on for the the other topics for the other quarters (new tab for Q2 and another one for Q3).
On top of that, I would like the macro to by dynamic and to identify which Topic needs to be inspected in which quarter based on the inspection date project. A sort of "if Topic A as an inspection date Q12020, then, it will go in the tab "Controls to be performed in Q1".
I would be so grateful if you can help me to write the script
))Many thanks in advance,
Best regards,
Ryo,
