Can someone help me with a VBA code for below requirement please.
I have a data in Source sheet that has weekending date, Sales Amount, Senior Manage and Manager as below. let us say the below data is in source sheet of the current workbook. Source sheet have data from previous week also. I have filtered and given the current weeks data alone.
We have another sheet as output which is as below which is from previous week. now if we chnage report date to - 10/17/2020 and run then code has to do the below steps.
1. once date is chnaged in Report date column to 10/17/2020, it has to filter the same in the weekending column in source sheet.
2. it has to check whether there is any change in terms of Managers under Senior Managers. in the above case (Jade has been moved from Sam to Hari)
3. if there is a new addition under Senior Manager then that new name to be inserted below Senior Manager.
4. It has to be pick the right column to publish the values which can be done by looking in to first row data to find the date that matches with Report Date in "Y" Column.
4. now it has to be publish the sales amount based on the Date and Name and sales column.
final output looks as below. no need to remove jade from Sam as it got some data from previous week.
In my sheet Senior Managers Name will be in bold to differentiate. Thank you in advance.
I have a data in Source sheet that has weekending date, Sales Amount, Senior Manage and Manager as below. let us say the below data is in source sheet of the current workbook. Source sheet have data from previous week also. I have filtered and given the current weeks data alone.
| Column A | Column D | Column H | Column I |
|---|---|---|---|
| WEEK_END_DATE | Sales | Senior Manager | Manager |
| 10/17/2020 | 40 | Hari | Max |
| 10/17/2020 | 30 | Sam | James |
| 10/17/2020 | 60 | Hari | Max |
| 10/17/2020 | 10 | Hari | Colin |
| 10/17/2020 | 20 | Hari | Jade |
| 10/17/2020 | 40 | Sam | Varun |
| 10/17/2020 | 50 | Sam | James |
| 10/17/2020 | 70 | Sam | Murat |
We have another sheet as output which is as below which is from previous week. now if we chnage report date to - 10/17/2020 and run then code has to do the below steps.
| A | B | C | D | Y | |
|---|---|---|---|---|---|
| Senior Manager/Manager | 10/3/2020 | 10/10/2020 | 10/17/2020 | Report date | |
| Hari | 80 | 60 | 10/10/2020 | ||
| Max | 40 | 40 | |||
| Colin | 40 | 20 | |||
| Sam | 90 | 80 | |||
| Varun | 20 | 30 | |||
| James | 20 | 20 | |||
| Murat | 30 | 20 | |||
| Jade | 20 | 10 |
1. once date is chnaged in Report date column to 10/17/2020, it has to filter the same in the weekending column in source sheet.
2. it has to check whether there is any change in terms of Managers under Senior Managers. in the above case (Jade has been moved from Sam to Hari)
3. if there is a new addition under Senior Manager then that new name to be inserted below Senior Manager.
4. It has to be pick the right column to publish the values which can be done by looking in to first row data to find the date that matches with Report Date in "Y" Column.
4. now it has to be publish the sales amount based on the Date and Name and sales column.
final output looks as below. no need to remove jade from Sam as it got some data from previous week.
| Senior Manager/Manager | 10/3/2020 | 10/10/2020 | 10/17/2020 | Report date | |
| Hari | 80 | 60 | 130 | 10/17/2020 | |
| Max | 40 | 40 | 100 | ||
| Colin | 40 | 20 | 10 | ||
| Jade | 20 | ||||
| Sam | 90 | 80 | 190 | ||
| Varun | 20 | 30 | 40 | ||
| James | 20 | 20 | 80 | ||
| Murat | 30 | 20 | 70 | ||
| Jade | 20 | 10 |
In my sheet Senior Managers Name will be in bold to differentiate. Thank you in advance.
