Compare the value of a cell with the name of a tab in VBA

Question

I have the following problem: I have 2 tabs a so-called "List" where are the names of the employees and the position that each occupies, this list with names changes monthly, as an example Official A - Post 1 / Official B - Office 2

It may be that the following month the list changes to: Official D - Office 2/ Official H - Office 10

This list shall be determined in advance for each month.

The second flap is called "Groundwork" in it is the name of the official, the date the official worked on that project, the number of hours and the description of the activity carried out. Still inside this spreadsheet there are several tabs named according to the position, ie, you have the tab Cargo 1, Cargo 2, etc. What I need to do, through VBA is check in the tab "List" the name of the official and the position he occupies and go in the tab "Groundwork" copy the information regarding the date, duration worked and job description of that employee on the list and allocate exactly in the tab referring to the position he is occupying that month. But there are two conditions in this copy, within the office tab must appear the dates between 21/[previous month] and 20/[subsequent month] in order, even if the official did not work on that date and when the dates appear duplicated, copy duplicity by placing the same date underneath each other.

Answer 1

To help you with this situation, I would like to please raise the following points:

• How is the name of the employees in the "List" tab updated? Someone copies and pastes the names every month or so?
• If yes, to bring the data list of each employee in the "List" tab, a PROCV function already solves your problem
• Could explain (and if possible, show images) about the conditions pointed at the end?

Thank you for your attention.

Answer 2

Hello. I adapted a spreadsheet as a suggestion to meet your needs following your requirements.

You must register employees as you normally do, in the List spreadsheet. As for positions, you must register in the spreadsheet Positions.

Next you make the releases through the Releases spreadsheet, where you will choose the employee according to the list and automatically the current position of the employee will be loaded. When you click the Roll button the data will be recorded in the Base sheet.

Now to solve the problem of each worksheet for positions you had, I created only a Report worksheet, where you can choose the desired position and the calculation period. Put the date from 21 to 20 the other month as you had said. By clicking process, the code will fetch all releases in this period and from this position in the Base sheet and present them in the order you said you need.

A caveat is that if you want all dates without activities to appear in the report, you should make a release only with date and title.

I wasn’t worried about error handling and full formatting. It might get better over time. Follow the file link on Github:

https://github.com/clovisdanielcosta/excel-vba/tree/master/src/Cargos and Activities.xlsm

I hope it helps.

Hugs.